Query an Audit Table

Transact-SQL
1

Hi,

Doing some custom work in a commercial document management system, and need to get the five last documents opened by a user.

Relevant Fields from the audit table.:

DocID, DatePerformed

Normally, this would just be a simple SELECT TOP 5 .... ORDER BY, however a user could open the same document n times in a row (on different Dates), and I only want that DocID once.
DISTINCT doesn't work because I need to select the DatePerformed to order by it, and then every row, even those with the same DocID, will be distinct.

SQL Server 2008.

Thank you.

2

You need a UserId or name or whatever, whatever is the technical term.

;WITH FivePrvOpens as
(
select *
, Row_Number() OVER (PARTION BY UserId ORDER BY DatePerformed DESC) Rn
)
SELECT *
FROM FivePrvOpens
WHERE Rn < 6;
3

WITH TIES might do the trick?

4 
SELECT TOP (5) *
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY UserID, DocID 
        ORDER BY DatePerformed DESC) AS row_num
    FROM table_name
    WHERE UserID = @UserID
) AS last_doc_access
WHERE row_num = 1
ORDER BY DatePerformed DESC
5

Perfect!! Works exactly as needed. Thank you very much.