Home » SQL & PL/SQL » SQL & PL/SQL » Get latest Access record for each User
icon8.gif  Get latest Access record for each User [message #227472] Wed, 28 March 2007 05:43 Go to next message
jasho
Messages: 2
Registered: June 2006
Junior Member
Hi,

Pls help.

I would like to get all user records that having latest access_datetime. How Can I do it in single SQL ?

User_id access_datetime status
12345 6/7/2002 17:00:33 A
12345 11/15/2002 09:52:52 C
12345 11/15/2002 13:26:39 A
43217 8/2/2004 14:08:30 A
43217 1/10/2006 09:37:38 B

Result :

User_id access_datetime status
12345 1/15/2002 13:26:39 A
43217 1/10/2006 09:37:38 B

Best Regards
Jas
Re: Get latest Access record for each User [message #227481 is a reply to message #227472] Wed, 28 March 2007 06:25 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

select user_id, access_time, status
from (
select user_id, access_time, status,
       row_number () over (partition by user_id order by access_time desc) rn
)
where rn = 1
/


Regards
Michel
Previous Topic: drop
Next Topic: Please kindly help in decode count
Goto Forum:
  


Current Time: Fri Dec 09 17:38:47 CST 2016

Total time taken to generate the page: 0.24295 seconds