Using MAX(Time) and MAX(Date) to get only the latest per group

Question

I want to get the latest highscore, per user, per game. My current query isn't working.

I have a SQL DB like the following:

player(string)  game(string)    score(int)  Date(Date)      time(Time)
jake            soccer          20          2016/02/26      10:00:00
jake            chess           50          2016/02/26      10:00:00
jake            soccer          40          2016/02/26      13:00:00
jake            chess           30          2016/02/26      13:00:00
jake            soccer          20          2016/02/26      15:00:00
jake            chess           60          2016/02/26      15:00:00
jake            soccer          80          2016/02/26      18:00:00
jake            chess           10          2016/02/26      18:00:00
mike            chess           30          2016/02/26      13:00:00
mike            soccer          20          2016/02/26      15:00:00
mike            chess           60          2016/02/26      15:00:00
mike            soccer          80          2016/02/26      18:00:00
mike            chess           10          2016/02/26      18:00:00

What I want to get out of it is:

jake            soccer          80          2016/02/26      18:00:00
jake            chess           10          2016/02/26      18:00:00
mike            soccer          80          2016/02/26      18:00:00
mike            chess           10          2016/02/26      18:00:00

I found out the Time column also has the date, so this should work. This is my current Query:

SELECT t1.*
FROM db t1
INNER JOIN (
    SELECT player, MAX(time) TS
    FROM db
    GROUP BY player
) t2 ON t2.player = t1.player and t2.TS = t1.time
ORDER BY score DESC";

EDIT: I'm getting lots of wrong rows. Basically. I'm getting them sorted by time, but not the date I now need to sort them not only by MAX(Time) but MAX(Date) as well. Or merge Date and Time in a new var

Are you running it in the db or the application?It should work as it is. — Mihai, Feb 26 '16 at 16:02
That's weird I created a test with your example data and query. It works and you get the desired output. — SQLChao, Feb 26 '16 at 16:03
My problem is with the Date after all. I'm getting lots of rows for 23:59:00 and the dates of 2015, for example. I need to sort by both MAX(Date) and MAX(Time). — cmf, Feb 26 '16 at 16:43
Replace time and ts in your query or my answer with that: convert(datetime,cast(date as nvarchar(10)) + ' '+ cast(time as nvarchar(10))) — gofr1, Feb 26 '16 at 17:02
@gofr1 not quite sure what to replace. Would you mind editing your answer to reflect the change? Seems promising! Thanks! — cmf, Feb 26 '16 at 19:04

gofr1 · Accepted Answer · 2016-02-27T05:05:46.583

1

To get the latest highscore, per user, per game, try this:

 ;WITH cte as (
 SELECT player, game, MAX(convert(datetime,cast([date] as nvarchar(10)) + ' '+ cast([time] as nvarchar(10)))) TS
 FROM db
 GROUP BY player, game)
 SELECT db.*
 FROM cte
 LEFT JOIN db ON cte.player = db.player and cte.game = db.game and cte.TS = convert(datetime,cast(db.[date] as nvarchar(10)) + ' '+ cast(db.[time] as nvarchar(10)))
 ORDER BY highscore DESC

edited Feb 27 '16 at 05:05

answered Feb 26 '16 at 15:59

gofr1

15,741
11
42
52

Thank you very much! This did it with only a couple minor bugs due to the DB being poorly built. Eventually they added a DATETIME so now I just used MAX(DT) and it works like a charm! – cmf Mar 02 '16 at 17:08
Great! It's right approach to keep date and time in same column! – gofr1 Mar 02 '16 at 17:33

score 1 · Answer 2 · answered Feb 27 '16 at 05:43

1

Try using ROW_NUMBER()

SELECT
      t1.*
FROM (
      SELECT
            *
          , ROW_NUMBER() OVER (PARTITION BY player ORDER BY [time] DESC) AS rn
      FROM db
      ) AS t1
WHERE rn = 1
;

answered Feb 27 '16 at 05:43

Paul Maxwell

33,002
3
32
51

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

Since you want not just the most recent game but also its score, this question can use the same patterns discussed here: Select first row in each GROUP BY group?

Personally I think picking the most recent time and then using that to match rows in the outer query is a little scary, since someone just might have two games at the same instant. Also it won't give you the best performance. Depending on your RDBMS, the linked question might have better approaches.

edited May 23 '17 at 11:58

Community

1
1

answered Feb 26 '16 at 16:16

Paul A Jungwirth

23,504
14
74
93

I get what you're trying to say, but in this case this dB is just a mockup. My actual dB is similar bit it's about servers, drives and disk space. The query happens once every 2 minutes so there's no same time, only same dates – cmf Feb 26 '16 at 19:12

Using MAX(Time) and MAX(Date) to get only the latest per group

3 Answers3