Join a single row from a table in MySQL

Question

I have two tables players and scores .

I want to create a report that looks something like this:

 player first score points foo 2010-05-20 19 bar 2010-04-15 29 baz 2010-02-04 13 

At the moment, my query looks something like this:

 select p.name player, min(s.date) first_score, s.points points from players p join scores s on s.player_id = p.id group by p.name, s.points 

I need s.points , which is associated with the string returned by min(s.date) . Does this happen with this request? That is, how can I be sure that I get the correct s.points value for the concatenated string?

Side note. I believe that this is due to the fact that MySQL does not have a dense rating. What is the best workaround here?

Answer 1

This is a problem with the largest-n-group that often occurs when stack overflows.

Here is my usual answer:

 select p.name player, s.date first_score, s.points points from players p join scores s on s.player_id = p.id left outer join scores s2 on s2.player_id = p.id and s2.date < s.date where s2.player_id is null ; 

In other words, given the score of s, try to find the score of s2 for the same player, but with an earlier date. If an early estimate is not found, then s is the earliest.

---

Repeat your comment about the ties: you must have a policy for which you can use in the case of a tie. One possibility is that you use automatic incremental primary keys, the one that has the least value is the earlier one. For an additional term in outer join, see below:

 select p.name player, s.date first_score, s.points points from players p join scores s on s.player_id = p.id left outer join scores s2 on s2.player_id = p.id and (s2.date < s.date or s2.date = s.date and s2.id < s.id) where s2.player_id is null ; 

Basically, you need to add tie-break conditions until you go to the column, which is guaranteed to be unique, at least for this player. The primary key of the table is often the best solution, but I have seen cases where another column fits.

As for the comments I shared with @OMG Ponies, remember that this type of query is highly beneficial from the right index.

Answer 2

Most RDMBs will not allow you to include non-aggregate columns in the SELECT clause when using GROUP BY. In MySQL, you get values ​​from random rows for your non-aggregate columns. This is useful if you really have the same value in a specific column for all rows. Therefore, it is nice that MySQL does not limit us, although this is important for understanding.

A whole chapter is devoted to this in SQL Antipatterns .