Q) How to Find Max Date from each Group? (Asked in Infosys
(INFI)Interview)
- 4 Answers
- 7240 Views
- Infosys, I also Faced
- E-Mail Answers
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 oracle handles dead locks?
What are the uses of Database Trigger ?
In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
Using the relations and the rules set out in the notes under each relation, write table create statements for the relations EMPLOYEE, FIRE and DESPATCH. You should aim to provide each constraint with a formal name, for example table_column_pk.
How can we force the database to use the user specified rollback segment?
How do I connect to oracle?
How to define a specific record type?
ABOUT IDENTITY?
10)In an RDBMS, the information content of a table does not depend on the order of the rows and columns. Is this statement Correct? A)Yes B)No C)Depends on the data being stored D)Only for 2-dimensional tables
What do you know about normalization? Explain in detail?
How would you best determine why your MVIEW couldnt FAST REFRESH?
In XIR2 if we lost the administration password .How can we regain the password?thanks in advance.
Oracle General 
