SELECT the oldest of the most recent lines

Question

I have a table storing the scores (with the date) of players they did at each game.

Example:
john  154  10/02/2014
mat   178  09/02/2014
eric  270  08/02/2014
mat   410  07/02/2014
john  155  06/02/2014

In this example I want "eric 270 08/02/2014" because thins is the oldest of the most recents. Which request must I do to retrieve that ?

...And how is 08/02/2014 the oldest record among those you listed (whether it is mm/dd/yyyy or dd/mm/yyyy)? — Michael Berkowski, Jul 21 '14 at 19:57
Can you further explain your question. Why don't you want to select 'mat 410 07/02/2014'? Since that is an older score. — Peter van der Wal, Jul 21 '14 at 20:03
I want eric because "mat 401 07/02/2014" is older than "mat 178 09/02/2014" — Entretoize, Jul 21 '14 at 21:45
@Greg2fs As I understand your question you want from the *most recent scores by user* the oldest one. — VMai, Jul 21 '14 at 22:04
I gave an example because it's hard for me to explain in english. Maybe it will be easier in 2 requests, the first one to get the most recent ones: (john 154 10/02/2014, mat 178 09/02/2014, eric 270 08/02/2014) and the second to get the oldest: eric 270 08/02/2014 — Entretoize, Jul 22 '14 at 07:23

score 2 · Accepted Answer · edited May 23 '17 at 10:25

2

As I understand it, you request the oldest entry among the set containing the most recent one of each user.

In such a case, you can deal with your problem using a subquery given the last date for each user, then used in the main query to select and sort only the most recent entry of each user.

SELECT scores.* 
FROM scores
INNER JOIN
(
  SELECT max(date) last, name
  FROM scores
  GROUP BY name
) last_temp_table
ON scores.name = last_temp_table.name
AND scores.date = last_temp_table.last
ORDER BY scores.date ASC LIMIT 1;

More info in different SO threads such as MySQL order by before group by

edited May 23 '17 at 10:25

Community

1
1

answered Jul 21 '14 at 21:24

user3786597

380
2
6

1

This seems to me the correct approach, only you've got to sort ASC to get the oldest one of the recents. – VMai Jul 21 '14 at 21:51
@VMai thanks you for pointing out my mistake on the sorting order. I just edit it. – user3786597 Jul 21 '14 at 21:58
I just tried that: SELECT * FROM (SELECT max(date) last, name FROM scores GROUP BY name) AS lasts ORDER BY last ASC LIMIT 1 – Entretoize Jul 22 '14 at 07:44
1

@Greg2fs With a simplified query like yours, you won't get the score right. Please have a look at the comments in my fiddle. – VMai Jul 22 '14 at 08:14
I don't see what's the problem, also like that http://sqlfiddle.com/#!2/262a1/7/0 it seems working without subquery, can you explain ? – Entretoize Jul 22 '14 at 08:34
Ok, I undestood you explaination and your query, thank you very much. – Entretoize Jul 22 '14 at 12:03
Just one more question, assuming the scores are taken from another table (games) can it include results that have no score yet (no row in games) – Entretoize Jul 25 '14 at 08:38
You can join two tables with LEFT JOIN. The query then returns all the rows of the left table even if there is no related rows in the right one. – user3786597 Jul 25 '14 at 09:08
Yes but then I get everything... I found (something like) that: `SELECT * FROM players,scores INNER JOIN ( SELECT max(date) last, name FROM scores,players GROUP BY name ) last_temp_table ON scores.date = last_temp_table.last WHERE (players.name = last_temp_table.name OR scores.date IS NULL) ORDER BY scores.date ASC LIMIT 1;` – Entretoize Jul 25 '14 at 09:31
still miss some results, even with LEFT JOIN :-( – Entretoize Jul 25 '14 at 10:19
it should works : `SELECT * FROM players LEFT JOIN (scores INNER JOIN (SELECT max(scores.date) last, name FROM scores GROUP BY name) last_temp_table ON last_temp_table.last=scores.date and last_temp_table.name=scores.name) ON players.name=scores.name ORDER BY scores.date ASC LIMIT 1;` – user3786597 Jul 28 '14 at 12:58

score 0 · Answer 2 · answered Jul 21 '14 at 20:16

The question as worded doesn't make much sense unless you define most recent.

If I assume that you have some criteria like: "Give the oldest event that happened within the last 3 days" then that is a simple matter of ordering and limiting across a date range.

select * from events where ts >= CURDATE() - 3
 order by ts asc
  limit 1

SELECT the oldest of the most recent lines

2 Answers2