how to show only last two rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to show only last two rows

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-06-24 : 03:29:23

Hi All,

I have a table with user name and login time. I would like to find out the last two login times of the user. If the user login single time in the day then it should show only 1 time else it should show 2 login times in the result.

Kindly help.

Table:
UserName LoginTime
AXX 2009-06-01 14:00
AXX 2009-06-01 16:00
AXX 2009-06-01 17:00
AXX 2009-06-01 18:00
BBC 2009-06-02 14:00
BBC 2009-06-02 15:00
BBC 2009-06-02 16:00
AXX 2009-06-02 14:00
AXX 2009-06-02 17:00
AXX 2009-06-02 18:00
XYZ 2009-06-02 16:00
XYZ 2009-06-02 17:00
XYZ 2009-06-03 14:00
AXX 2009-06-03 14:00
MNO 2009-06-03 17:00

Expecting Result is:
UserName LoginTime
AXX 2009-06-01 17:00
AXX 2009-06-01 18:00
BBC 2009-06-02 15:00
BBC 2009-06-02 16:00
AXX 2009-06-02 17:00
AXX 2009-06-02 18:00
XYZ 2009-06-02 16:00
XYZ 2009-06-02 17:00
XYZ 2009-06-03 14:00
AXX 2009-06-03 14:00
MNO 2009-06-03 17:00

Regards,
Ganny.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 03:32:41

SELECT UserName, LoginTime
FROM (
SELECT UserName, LoginTime, ROW_NUMBER() OVER (PARTITION BY UserName,
DATEDIFF(DAY, 0, LoginTime) ORDER BY LoginTime DESC) AS recID FROM Table1
) AS f
WHERE f <= 2
ORDER BY UserName, LoginTime

E 12°55'05.63"
N 56°04'39.26"