SQL; select last record each group

Question

My table looks like this:

id userid score
1 10 55
2 10 89
3 10 70
4 15 50
5 15 80
6 17 90
7 17 99

I need such an SQL statment, which selects LAST score for each userid. This should get selected:

id userid score
3 10 70
5 15 80
7 17 99

any ideas? Thanks

P.S. I use SQLite3

you must add tag, which db you use. Certainly, with `partition` and `order by`, this is easy. Or even `RANK` — T.S., Dec 15 '22 at 23:24

score 2 · Answer 1 · answered Dec 15 '22 at 23:35

as mentioned by T.S. some sort of windowing function. here is one with row number assuming your id can be used to determine what is "last"

with t as
 (select m.*,
  row_number() over (partition by userid order by id desc) as rn
  from mytable m
)
select id, userid, score from t where rn = 1

Wolric · Answer 2 · 2022-12-15T23:45:55.287

A similar approach to this issue by first sorting the table in ascending order of userid and descending order of id. Then grouping the values of the inner selection by the outer userid

The inner selection

SELECT inner.* FROM table AS inner ORDER BY userid ASC, id DESC;

will give you this intermediate result:

id  userid  score
--  ------  -----
3   10      70   
2   10      89   
1   10      55   
5   15      80   
4   15      50   
7   17      99   
6   17      90

Grouping this result by the userid will give you the outcome you want.

SELECT outer.* FROM 
  (SELECT inner.* FROM table AS inner ORDER BY userid ASC, id DESC) AS outer
GROUP BY outer.userid;

This gives you:

id  userid  score
--  ------  -----
3   10      70   
5   15      80   
7   17      99

SQL; select last record each group

2 Answers2