CODEWITHSUNDEEP
sqlsql-servert-sql
Royi Namir
Royi Namir

Reputation: 148514

How to find the last row in a column with duplicate values

Someone in my company wrote a closed stored procedure which gives these results:

rowNumber   userid  DateCreated
1             500   2011-07-20 11:03:24.590
2             500   2011-07-21 11:03:24.590
3             500   2011-07-22 11:03:24.590
1             502   2011-07-20 11:03:24.590
2             502   2011-07-21 11:03:24.590
1             504   2011-07-20 11:03:24.590
2             504   2011-07-21 11:03:24.590

I cannot change the original query which uses the row_number, so the above list is my read-only datasource.

I need to get the full data for each userId by its max rownumber, i.e.

 3            500   2011-07-22 11:03:24.590
 2            502   2011-07-21 11:03:24.590
 2            504   2011-07-21 11:03:24.590

It is important that I don't use the rownumber again.

Upvotes: 0

Views: 865

Answers (2)

Jean-Yves Pagès
Jean-Yves Pagès

Reputation: 21

SELECT test.rownumber, test.userid, test.datecreated
FROM test
     INNER JOIN (SELECT MAX(rownumber) AS Expr1, userid
                 FROM test AS test_1
                 GROUP BY userid) AS t1
     ON test.userid = t1.userid AND test.rownumber = t1.Expr1
     ORDER BY test.userid

Upvotes: 0

Derek
Derek

Reputation: 23228

select t.* 
from 
(
    select userid, max(rownumber) m 
    from table
    group by userid
) c
    inner join table t
         on c.userid = t.userid and c.m = t.rownumber

Upvotes: 1

Related Questions