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 |
What is partitioned table in Oracle?
How to unlock the sample user account in oracle?
How to execute a stored program unit?
How to end the current transaction in oracle?
What are privileges and grants?
How to load excel data sheet to oracle database
i have a question here... As of my knowledge, when we apply an index (b-tree)on a column, internally it arranges the data in b-tree format and do the fetching process correspondingly... and my quetion is... How a bit-map index arranges the data internally when applied on a column?IS it in b-tree format or whatelse?
Explain the concept of the DUAL table.
What is key preserved table?
What is define in oracle?
What is a static data dictionary in oracle?
What is a nested table and how is it different from a normal table?