SQL; select last record each group-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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