How to select most recent record for each with a different foreign key

Question

I have a table that has the following structure.

+--+------+---+-------------------+
|id|cardId|elo|timestamp          |
+--+------+---+-------------------+
|1 |000ac9|150|2018-12-19 17:49:16|
|2 |000ac9|160|2018-12-19 17:59:16|
|3 |000ad9|140|2018-12-19 18:21:16|
|4 |000ac9|130|2018-12-19 17:59:16|
|5 |000ad9|260|2018-12-19 19:01:16|
+--+------+---+-------------------+

i would like to get the most recent elo for each cardId.

I have tried to used the Distinctive key word but that doesn't always give me the most recent entry into the database.

I expect something like the following.

+--+------+---+-------------------+
|id|cardId|elo|timestamp          |
+--+------+---+-------------------+
|4 |000ac9|130|2018-12-19 17:59:16|
|5 |000ad9|260|2018-12-19 19:01:16|
+--+------+---+-------------------+

You have two values for `cardId=000ac9` with the same timestamp. If that is possible in the real data, do you want both values or just one? If just one, which one? — Nick, Dec 23 '18 at 00:57

score 0 · Accepted Answer · answered Dec 22 '18 at 23:49

0

You can use the ROW_NUMBER() window function, available in MySQL 8.x:

select * from (
  select
    id,
    cardId,
    elo,
    timestamp,
    row_number() over(partition by cardId order by timestamp desc) as rn
  from my_table
) x
where rn = 1

answered Dec 22 '18 at 23:49

The Impaler

45,731
9
39
76

How to select most recent record for each with a different foreign key

1 Answers1