Get latest sql rows based on latest date and per user

Question

I have the following table:

RowId, UserId, Date
1, 1, 1/1/01
2, 1, 2/1/01
3, 2, 5/1/01
4, 1, 3/1/01
5, 2, 9/1/01

I want to get the latest records based on date and per UserId but as a part of the following query (due to a reason I cannot change this query as this is auto generated by a tool but I can write pass any thing starting with AND...):

SELECT RowId, UserId, Date
FROM MyTable
WHERE 1 = 1
AND ( 

// everything which needs to be done goes here . . .

)

I have tried similar query, but get an error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

EDIT: Database is Sql Server 2008

_per UserID_ means that you want to group by `UserID`. How do you want to group with a `Where` clause only? — Tim Schmelter, Nov 10 '13 at 21:37
Database is sql server 2008, yes i want to group by user_id, can be done in any way. — Umair, Nov 10 '13 at 21:51

Andriy M · Accepted Answer · 2013-11-10T22:19:31.747

3

You could use a NOT EXISTS condition:

SELECT RowId, UserId, Date
FROM MyTable
WHERE 1 = 1
AND NOT EXISTS (
  SELECT *
  FROM MyTable AS t
  WHERE t.UserId = MyTable.UserId
    AND t.Date > MyTable.Date
)
;

Note that if a user has more than one row with the same latest Date value, the query will return all such entries. If necessary, you can modify the subquery's condition slightly to make sure only one row is returned:

  WHERE t.UserId = MyTable.UserId
    AND (t.Date > MyTable.Date
      OR t.Date = MyTable.Date AND t.RowId > MyTable.RowId
    )

With the above condition, if two or more rows with the same Date exist for the same user, the one with the greater RowId value will be returned.

edited Nov 10 '13 at 22:19

answered Nov 10 '13 at 22:00

Andriy M

76,112
17
94
154

I like this, short and readable. +1 However, if there are multiple records with a max-date for one `UserID` and OP wants only one row... – Tim Schmelter Nov 10 '13 at 22:08
@TimSchmelter: That is a good point, thanks. I'll add a note on that and a workaround. – Andriy M Nov 10 '13 at 22:14
Or in subquery we can use `TOP 1` with order by `Date` in `DESC` order, so in that case will get the latest record by date. – Umair Nov 11 '13 at 09:15
@Umair: I am not sure I understand what subquery you are talking about but if you mean the one used in my answer, it would certainly *not* work that way. `TOP n`, whether with or without `ORDER BY`, would not affect the results of an EXISTS subquery in any way. – Andriy M Nov 11 '13 at 09:48
I was thinking to add in `SELECT * FROM MyTable AS t WHERE t.UserId = MyTable.UserId AND t.Date > MyTable.Date`, but yes the where query you mentioned does work for me. – Umair Nov 11 '13 at 10:32

score 1 · Answer 2 · 2013-11-10T21:50:33.463

1

Try:

SELECT RowId, UserId, Date
FROM MyTable
WHERE 1 = 1
AND EXISTS
(SELECT 1
 FROM (SELECT UserId, MAX(Date) MaxDate
       FROM MyTable
       GROUP BY UserId) m
 WHERE m.UserId = MyTable.UserId and m.MaxDate = MyTable.Date)

SQLFiddle here.

edited Nov 10 '13 at 21:50

answered Nov 10 '13 at 21:45

If there are multiple records with a max-date for one `UserID` and OP wants only one row, how could you modify this query? http://sqlfiddle.com/#!6/84b97/1/0 – Tim Schmelter Nov 10 '13 at 22:12
@TimSchmelter: The user said "I want to get the latest records" - note the use of the plural; I understand this to mean that if there are multiple records for the latest date for the same user, then OP wants all of them. Of course, I could be wrong, in which case I would need to know whether `RowId`, or some other column, could be used to determine which is the latest; if so, I would remove the grouping, add `RowId` to the inner query and change the `MAX(Date)` to be a windowed `rownumber`, then change the EXISTS join to be on `rownumber`=1 and on `RowID` - eg: http://sqlfiddle.com/#!6/84b97/6 – Nov 11 '13 at 06:32
The plural would be correct also with one record per UserId. However, the row_number approach works fine. But i would remove the second order by RowId if it spent matter because you could replace it with Dense_Rank easily to get the "multi-row" behaviour. – Tim Schmelter Nov 11 '13 at 07:33

score 1 · Answer 3 · answered Nov 10 '13 at 21:45

Assuming you have the ability to modify anything within the AND clause you can do a query like this if you are using TSQL

SELECT RowId, UserId, [Date]
FROM @myTable
WHERE 1 = 1
AND ( 
    RowId IN (
            SELECT D.RowId
            FROM (
                SELECT DISTINCT MAX(RowId) AS RowId, UserId, MAX([Date]) AS [Date]
                FROM @myTable
                GROUP BY UserId
        ) AS D
    )
)

Tim Schmelter · Answer 4 · 2013-11-10T21:58:01.743

1

Assuming that RowID is an identity column:

SELECT t1.RowId, t1.UserId, t1.Date
FROM MyTable t1
WHERE 1 = 1
AND t1.RowID IN ( 

    SELECT TOP 1 t2.RowID 
    FROM MyTable t2
    WHERE  t1.UserId = t2.UserId
    AND    t2.Date = (SELECT MAX(t3.Date) FROM MyTable t3
                      WHERE t2.UserID = t3.UserId)

)

Demo

edited Nov 10 '13 at 21:58

answered Nov 10 '13 at 21:51

Tim Schmelter

450,073
74
686
939

Get latest sql rows based on latest date and per user

4 Answers4