MySQL select distinct records

Question

I'm trying to write a query that will pull out the "best" record from a list of values:

SELECT s.swimmerName, r.resultTimeText, r.resultAgeGroup, r.resultEventID, v.venueName     
FROM tblResults r
JOIN tblEvents e ON e.eventID = r.resultEventID
JOIN tblSwimmers s ON r.resultSwimmerID = s.swimmerID
JOIN tblVenues v ON e.resultVenueID = v.venueID
WHERE s.swimmerGender = %s
AND r.resultStroke = %s
GROUP BY s.swimmerName

This selects all of my records but people are listed twice with different times (a consequence of the DISTINCT I know). What would be the best way to select the best time for each person?

Try this [stackoverflow](http://stackoverflow.com/questions/5657446/mysql-query-max-group-by). — Sam, Jan 09 '14 at 21:46
You could use max () on time field. See http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column.html — makallio85, Jan 09 '14 at 21:47
Times are stored in text, I did try to have them as numbers, but they're in the format MM:SS.HH — jackacon, Jan 09 '14 at 21:48
Lol. I mean min () because it looks like less is better here http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_min — makallio85, Jan 09 '14 at 21:50
None of the answers provided above are quite right but luckily it doesn't matter because this question is asked and correctly answered every single day. — Strawberry, Jan 09 '14 at 21:51
Sorry to be thick, but could you point me in the right direction? I wasn't sure how to word the question. — jackacon, Jan 09 '14 at 21:54
Take a look in the manual... http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html — Strawberry, Jan 09 '14 at 22:05
I think I'm getting there (read that doc Strawberry, most of it went right over my head). I've changed the ORDER BY for GROUP BY, which has returned unique names, I just need to work out how to order them by the time. When I have a ORDER BY and a GROUP BY it just falls over. — jackacon, Jan 09 '14 at 22:39

Bohemian · Accepted Answer · 2014-01-10T00:47:33.330

0

You can use mysql's quirky grouping to help you:

SELECT * FROM (
  SELECT s.swimmerName, r.resultTimeText, r.resultAgeGroup, r.resultEventID, v.venueName     
  FROM tblResults r
  JOIN tblEvents e ON e.eventID = r.resultEventID
  JOIN tblSwimmers s ON r.resultSwimmerID = s.swimmerID
  JOIN tblVenues v ON e.resultVenueID = v.venueID
  WHERE s.swimmerGender = %s
  AND r.resultStroke = %s
  ORDER BY 2) x
GROUP BY swimmerName
ORDER BY 2

This works by first ordering the data fastest to slowest, then grouping by name. In myswl (only) grouping by not all non-aggregated columns has the effect of filtering only the first row encountered for each unique combination of grouped by colums.

edited Jan 10 '14 at 00:47

answered Jan 09 '14 at 22:52

Bohemian

412,405
93
575
722

this gives me "Unknown column 's.swimmerName' in 'group statement'". That a problem with Nightmareweaver? – jackacon Jan 09 '14 at 22:59
Ah thanks much! It's now running OK, but is in alphabetical instead of time order. I'm guessing the ORDER BY 2 refers to r.resultTimeText? – jackacon Jan 09 '14 at 23:06
Yes. What format is it in? Can you provide some samples? (Don't worry... We'll get this working!) – Bohemian Jan 09 '14 at 23:12
Time text is a text field in the format MM:SS.HH (i.e. 01:23.54) I've got a screen shot here (sorry had to blank names, minors and all that...) https://www.dropbox.com/s/qrbnxb386damxaw/names%20list.png – jackacon Jan 09 '14 at 23:18
Apart from there being bad data (missing leading zeroes eg for "44.31" (which you should fix by updating the data) it should work, because alphabetical ordering should be the same as time order. The key is that there are always the same number of digits in each value. What isn't working exactly? – Bohemian Jan 09 '14 at 23:52
It needs to be sorted by the time field, not the name field. But you're right it is working, I just have the final hurdle to get round! – jackacon Jan 09 '14 at 23:56
If you want it *also* sorted by time, add another order by at the end - see edited answer – Bohemian Jan 10 '14 at 00:47

MySQL select distinct records

1 Answers1