CODEWITHSUNDEEP
sqlsql-serversql-server-2008group-bysql-order-by
JGrimm
JGrimm

Reputation: 499

SQL Server Group By Order By Where

I have a single table which I need to pull back the 5 most recent records based on a userID and keying off of documentID (no duplicates). Basically, I'm tracking visited pages and trying to pull back the 3 most recent by user.

Sample data:

╔══════════════════════════════════════════════╗
║UserID   DocumentID   CreatedDate             ║
╠══════════════════════════════════════════════╣
║  71         22       2013-09-09 12:19:37.930 ║
║  71         25       2013-09-09 12:20:37.930 ║
║  72          1       2012-11-09 12:19:37.930 ║
║  99         76       2012-10-10 12:19:37.930 ║
║  71         22       2013-09-09 12:19:37.930 ║
╚══════════════════════════════════════════════╝

Desired query results if UserID = 71:

╔══════════════════════════════════════════════╗
║UserID    DocumentID  CreatedDate             ║
╠══════════════════════════════════════════════╣
║  71         25       2013-09-09 12:20:37.930 ║
║  71         22       2013-09-09 12:19:37.930 ║
╚══════════════════════════════════════════════╝

Upvotes: 14

Views: 73401

Answers (4)

kishore krv
kishore krv

Reputation: 70

Select USERID,DOCUMENT ID 
FROM yourtable
QUALIFY ROW_NUMBER OVER(Partition by user id ORDER By document id Desc)<6

This works in Teradata. Hope this works in Sql Server too as its mainly ANSI SQL.

Upvotes: -1

Tom
Tom

Reputation: 2230

SELECT TOP 3 UserId, DocumentId, MAX(CreatedDate)
FROM MyTable
WHERE UserId = 71
GROUP BY UserId, DocumentId
ORDER BY MAX(CreatedDate) DESC

Upvotes: 30

Gidil
Gidil

Reputation: 4137

You could try this:

SELECT DISTINCT USERID, 
                DOCUMENTID, 
                MAX(CREATEDDATE) 
                  OVER ( 
                    PARTITION BY USERID, DOCUMENTID) CREATEDDATE 
FROM   MYTABLE 
WHERE  USERID = 71

Take a look at the working example on SQL Fiddle.

Good Luck!

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166396

You could try using a CTE and ROW_NUMBER.

Something like

;WITH Vals AS (
    SELECT UserID, 
           DocumentID, 
           ROW_NUMBER() OVER(PARTITION BY UserID, DocumnentID ORDER BY CreatedDate DESC) RowID
    FROM MyTable
)
SELECT TOP 3 *
FROM Vals
WHERE UserID = 71
AND RowID = 1

Upvotes: 1

Related Questions