Q) How to Find Max Date from each Group? (Asked in Infosys
(INFI)Interview)
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
How to lock and unlock a user account in oracle?
For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame ?
What is partitioned table in Oracle?
1) Does oracle have any table which contain all the exceptions and it's code internally?
What is oracle sid?
Explain the different normalization forms?
When can Hash Cluster used ?
Table1- have two column filename data AFGDFD-20112011 hi how r u bsdasd-23042011 name shoud be in bold Now i want output like filename data AFGDFD hi how r u bsdasd name shoud be in bold Kindly answer this
How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.
What do you mean by merge in oracle?
What are the sql clauses supported in the link property sheet ?
What are the various oracle database objects?