Fetching Rows By Minimum Scores From MySQL Database

Question

I have a database which records golf scores by people. Each person has arbitrarily many scores as there is no hard limit to games of golf.

Along with each Person and Score, the Date (stored as Unix Time but here shown normally) and some other data (like the Course and Weather) is recorded.

My issues are as follows: 1) I only want the oldest "top" score from each player. Meaning if a player's best was score Par (regardless of Course) and was done 3 times, I only want the very first one. My code does not handle this and I don't know how to do so. It outputs all instances of a player's best score.

2) I want to also be able to grab data from specific subsets of the data. Example: FROM golfdata WHERE Course = $course_name or by weather conditions etc. My solutions makes me do this twice. Is there a way to not repeat myself?

This is as close as I could get:

SELECT a.*
FROM golfdata a
INNER JOIN (
SELECT Name, MIN(Score) Score
FROM golfdata b
GROUP BY Name) c 
    ON a.Name = c.Name AND a.Score = c.Score

If I want to filter by course I use:

(SELECT * FROM golfdata WHERE Course = $course_name) in place of "golfdata"

Sample data, much more in reality.

+--------+------------+-------+-------------+------+
| Name   | YYYY-MM-DD | Score | Location    | Wind |
+--------+------------+-------+-------------+------+
| Tom    | 2019-5-25  | 70    | Augusta     | Fast |
| Amanda | 2019-1-13  | 73    | Augusta     | Calm |
| David  | 2018-12-25 | 69    | Pine Valley | Slow |
| David  | 2018-9-15  | 72    | Oakmont     | Calm |
| David  | 2018-5-19  | 71    | Pine Valley | Slow |
| Tom    | 2018-8-11  | 70    | Oakmont     | Calm |
+--------+------------+-------+-------------+------+

Expected output:

+--------+------------+-------+-------------+------+
| Name   | YYYY-MM-DD | Score | Location    | Wind |
+--------+------------+-------+-------------+------+
| Amanda | 2019-1-13  | 73    | Augusta     | Calm |
| David  | 2018-12-25 | 69    | Pine Valley | Slow |
| Tom    | 2018-8-11  | 70    | Oakmont     | Calm |
+--------+------------+-------+-------------+------+

Note Tom's oldest "best" score was selected regardless of course.

Further info: I am on MySQL v5.7 if that helps at all.

I would include a surrogate key in this instance – Strawberry Jun 13 '19 at 07:24 — Strawberry, Jun 13 '19 at 07:24
@Strawberry I don't quite know what that means. – royco Jun 13 '19 at 07:28 — royco, Jun 13 '19 at 07:28

score 0 · Accepted Answer · answered Jun 13 '19 at 07:38

0

Perhaps something like this:

SELECT a.name,MIN(a.date) DATE,c.Score,a.Location,a.Wind 
  FROM golfdata a
  INNER JOIN 
  (SELECT NAME,MIN(score) score FROM golfdata b GROUP BY NAME) c
ON a.Name=c.Name AND a.Score=c.Score
GROUP BY a.NAME,c.score;

I added MIN(a.date) DATE in the select basically to get the smallest date whenever the score is the same. Then I added GROUP BY a.NAME, c.score at the outer query.

answered Jun 13 '19 at 07:38

FanoFN

6,815
2
13
33

there is no guarantee that the other columns returned from `a` (a.name, a.location, a.wind) will be from the same row as the row that the minimum `a.date` is returned from. This query relies on a MySQL extension to behavior of GROUP BY, and will throw an error when that extension is disabled by including `ONLY_FULL_GROUP_BY` in mysql_mode. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html – spencer7593 Jun 14 '19 at 14:42

Fetching Rows By Minimum Scores From MySQL Database

1 Answers1