Home » SQL & PL/SQL » SQL & PL/SQL » Get the Recent Record (Merged)
Get the Recent Record (Merged) [message #237558] Mon, 14 May 2007 21:09 Go to next message
pradkuamr
Messages: 29
Registered: November 2006
Junior Member
Hi All

I am having the a table(1 million) with data as follows:

Input:
Col1 Col2 Col3
1 1895 4/7/2007
2 1895 4/7/2007
3 1895 3/5/2005
4 6600 3/3/2007
5 6600 3/3/2007
6 6600 3/1/2006
so on


Now I would like to have my output as follows:

Desired Output:
Col1 Col2 Col3
1 1895 4/7/2007
4 6600 3/3/2007

I tried using the following query for one id.

select * from T1 A
where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
WHERE A.Col2=C.Col2)
and A.Col2=1895
AND ROWNUM<2

This worked fine and I got the output for 1895

When I tried using this
select * from T1 A
where A.Col3=(SELECT MAX(C.Col3) FROM T1 C
WHERE A.Col2=C.Col2)
AND ROWNUM<2

on bulk data, it is giving only one record. I assume this is due to rownum<2.
Please share your ideas how can i apply that to a full table
Re: Get the recent record [message #237569 is a reply to message #237558] Mon, 14 May 2007 23:40 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Post your Oracle version
2/ Format your post (queries, chart...), read How to format your posts
3/ Also post in words what you want to do not just the output chart
4/ Post a test case (create table and insert statements)

It seems you want the latest row defined by Col3 for each distinct Col2:
with 
  data as (
    select col1, col2, col3,
           row_number() over (partition by col2 order by col3 desc) rn
    from t1
  )
select col1, col2, col3
from data
where rn = 1
order by col2
/

Regards
Michel
Previous Topic: Your Session Has Expired!
Next Topic: How to create triggers
Goto Forum:
  


Current Time: Mon Dec 05 07:06:55 CST 2016

Total time taken to generate the page: 0.14847 seconds