SQL Server Group By Order By Where

Question

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 ║
╚══════════════════════════════════════════════╝

Have you tried writing the script yourself? If so, please post it even if was erroneous. — PM 77-1, Sep 09 '13 at 17:53

Tom · Accepted Answer · 2013-09-09T18:28:30.263

29

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

edited Sep 09 '13 at 18:28

answered Sep 09 '13 at 17:56

Tom

2,180
7
30
48

1

quick question: this would not work in MySQL since it does the ordering after grouping (it would execute without error, but it would produce results that may or may not be 'correct'); can someone confirm that this would work in SQL Server, and is thus a difference between the 2 engines? – sam-6174 Mar 17 '16 at 21:19
@user2426679 - this did work for me using Sql Server 2016. – Nelda.techspiress Jul 06 '17 at 19:48

score 1 · Answer 2 · answered Sep 09 '13 at 17:55

1

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

answered Sep 09 '13 at 17:55

Adriaan Stander

162,879
31
289
284

I don't think that your query returns the required CreatedDate. Correct me if I'm wrong. – Gidil Sep 09 '13 at 18:05

score 1 · Answer 3 · answered Sep 09 '13 at 18:02

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!

score -1 · Answer 4 · edited Dec 22 '15 at 07:48

-1

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.

edited Dec 22 '15 at 07:48

Ajay2707

5,690
6
40
58

answered Sep 09 '13 at 18:00

kishore krv

70
2

1

I don't think `QUALIFY` is ANSI SQL. – Gordon Linoff Sep 09 '13 at 18:01
select USERID,DOCUMENT ID,ROW_NUMBER OVER(Partition by user id ORDER By document id Desc) Rownum FROM yourtable having rownum<6 – kishore krv Sep 09 '13 at 18:11

SQL Server Group By Order By Where

4 Answers4