Q) How to Find Max Date from each Group? (Asked in Infosys (INFI)Interview)

Q) How to Find Max Date from each Group? (Asked in Infosys
(INFI)Interview)

Question Posted / niladri chatterjee

4 Answers
6948 Views
Infosys, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Q) How to Find Max Date from each Group? (Asked in Infosys (INFI)Interview) ..

Answer / niladri chatterjee

SQL> Select * From Market;

MARKET_ID MKT_NAME AREA SALE_DT
---------------------- -------- ---- ----------
1 uss NE 25-JAN-12
1 uss NE 24-FEB-12
1 uss NE 20-JUN-11
1 uss NE 15-MAR-11
2 rus SE 21-MAR-11
2 rus NE 24-APR-11
3 ger SE 20-FEB-11
3 ger NE 22-MAR-11
3 ger NE 24-FEB-12

My Answers:-

For the Single Max Row:

Select * From (Select * From market Order By Sale_Dt Desc)
Where rownum = 1;

Followings are for each Groups:

Select *
from market a
where a.sale_dt =
(select max(b.sale_dt) from market b
where a.market_id = b.market_id);

OR

select market_id, mkt_name, max(sale_dt)
from market
group by market_id, mkt_name;

Is This Answer Correct ?

9 Yes

1 No

Q) How to Find Max Date from each Group? (Asked in Infosys (INFI)Interview) ..

Answer / sudipta santra

select market_id, mkt_name, max(sale_dt)
from market
group by market_id, mkt_name;

Note: This is the only correct answer

Is This Answer Correct ?

5 Yes

0 No

Q) How to Find Max Date from each Group? (Asked in Infosys (INFI)Interview) ..

Answer / d ashwin

AS OUR EXAMPLE HR SCHEMA FOR GROUP WISE MAX DATE...

SELECT * FROM HR.EMPLOYEES
WHERE HIRE_DATE IN
(SELECT MAX(HIRE_DATE) FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID);

FOR SINGLE ROW MAX DATE...
SELECT * FROM
(
SELECT * FROM HR.EMPLOYEES
ORDER BY HIRE_DATE DESC)
WHERE ROWNUM = 1;

Is This Answer Correct ?

4 Yes

2 No

Q) How to Find Max Date from each Group? (Asked in Infosys (INFI)Interview) ..

Answer / suman rana

select market_id, mkt_name, sale_dt from (
select market_id, mkt_name, sale_dt, max(sale_dt) over
(partition by market_id, mkt_name ) Max_sale_dt
from market )
where sale_dt = Max_sale_dt

Is This Answer Correct ?

0 Yes

0 No

Post New Answer