CODEWITHSUNDEEP
sql-server-2008
Marcus25
Marcus25

Reputation: 883

Getting the most recent of a group of repeated rows

I have a table 'UserLibrary'

   Name  Library    AccessedTime

    A      ABC       2:00 am
    B      XYZ       4:00 am
    C      PQR       5:00 am 
    C      PQR       12:00 pm 
    C      PQR       2:00 pm
    D      LIT       4:00 pm

I want a list of distinct users, Library and their last accessed time.

Select * from UserLibrary, will give me repeated rows for user 'C' and Library 'PQR' , but i want the last accessed time only for user C.

so basically i Want,

   Name  Library    AccessedTime

    A      ABC       2:00 am
    B      XYZ       4:00 am 
    C      PQR       2:00 pm
    D      LIT       4:00 pm

How can i do this in Sql server

Upvotes: 0

Views: 33

Answers (2)

Free2Rhyme2k
Free2Rhyme2k

Reputation: 554

select Name, Library, max(AccessedTime) as 'AccessedTime'
from UserLibrary
group by Name, Library

Upvotes: 1

John Woo
John Woo

Reputation: 263933

You can use ROW_NUMBER() which generates sequential number which you can filtered with. In this case, the sequential number is generated for every name and library and sorted on the latest AccessedTime. The latest records will have a value of 1 on rn.

SELECT Name, Library, AccessedTime
FROM
(
    SELECT  Name, Library, AccessedTime,
            ROW_NUMBER() OVER ( PARTITION BY Name, Library
                                ORDER BY AccessedTime DESC) rn
    FROM    UserLibrary
) a
WHERE   a.rn = 1

Upvotes: 1

Related Questions