SQL Query sorting rows by duplicate name keeping lowest in result

Question

I've got a table with 11 columns and I want to create a query that removes the rows with duplicate names in the Full Name's column but keeps the row with the lowest value in the Result's column. Currently I have this.

SELECT
    MIN(sql363686.Results2014.Result),
    sql363686.Results2014.Temp,
    sql363686.Results2014.Full Name,
    sql363686.Results2014.Province,
    sql363686.Results2014.BirthDate,
    sql363686.Results2014.Position,
    sql363686.Results2014.Location,
    sql363686.Results2014.Date   
FROM
    sql363686.Results2014
WHERE
    sql363686.Results2014.Event = '50m Freestyle'
AND sql363686.Results2014.Gender = 'M'
AND sql363686.Results2014.Agegroup = 'Junior'
GROUP BY
    sql363686.Results2014.Full Name
ORDER BY
    sql363686.Results2014.Result ASC ;

At first glance it seems to work fine and I get all the correct values, but I seem to be getting a different (wrong) value in the Position column then what I have in my database table. All other values seem to be right. Any ideas on what I'm doing wrong?

I'm currently using dbVisualizer connected to a mysql database. Also, my knowledge and experience with sql is the bare mimimum

You are only grouping by one of your non-aggregated columns, which MySQL will allow you to do for some crack-smoking reason. Include all your non-aggregated columns in your group by and try again. — Andrew, Jan 15 '15 at 22:31
Is it really necessary to state the db each time? It's always the same db ?!!?! — Strawberry, Jan 15 '15 at 23:37

score 1 · Answer 1 · answered Jan 15 '15 at 22:41

1

Use group by and a join:

select r.*
from sql363686.Results2014 r
     (select fullname, min(result) as minresult
      from sql363686.Results2014 r
      group by fullname
     ) rr
     on rr.fullname = r.fullname and rr.minresult = r.minresult;

answered Jan 15 '15 at 22:41

Gordon Linoff

1,242,037
58
646
786

score 0 · Accepted Answer · answered Jan 15 '15 at 23:01

You have fallen into the trap of the nonstandard MySQL extension to GROUP BY.

(I'm not going to work with all those fully qualified column names; it's unnecessary and verbose.)

I think you're looking for each swimmer's best time in a particular event, and you're trying to pull that from a so-called denormalized table. It looks like your table has these columns.

Result
Temp
FullName
Province
BirthDate
Position
Location
Date   
Event
Gender
Agegroup

So, the first step is to locate the best time in each event for each swimmer. To do this we need to make a couple of assumptions.

A person is uniquely identified by FullName, BirthDate, and Gender.
An event is uniquely identified by Event, Gender, Agegroup.

This subquery will get the best time for each swimmer in each event.

SELECT MIN(Result) BestResult,
       FullName,BirthDate, Gender,
       Event, Agegroup
  FROM Results2014
 GROUP BY FullName,BirthDate, Gender, Event, Agegroup

This gets you a virtual table with each person's fastest result in each event (using the definitions of person and event mentioned earlier).

Now the challenge is to go find out the circumstances of each person's best time. Those circumstances include Temp, Province, Position, Location, Date. We'll do that with a JOIN between the original table and our virtual table, like this

SELECT resu.Event,
       resu.Gender,
       resu.Agegroup,
       resu.Result,
       resu.Temp.
       resu.FullName,
       resu.Province,
       resu.BirthDate,
       resu.Position,
       resu.Location,
       resu.Date
  FROM Results2014 resu
  JOIN (
               SELECT MIN(Result) BestResult,
                      FullName,BirthDate, Gender,
                      Event, Agegroup
                 FROM Results2014
                GROUP BY FullName,BirthDate, Gender, Event, Agegroup
       ) best   
           ON  resu.Result    = best.BestResult
          AND  resu.FullName  = best.FullName
          AND  resu.BirthDate = best.BirthDate
          AND  resu.Gender    = best.Gender
          AND  resu.Event     = best.Event
          AND  resu.Agegroup  = best.Agegroup
 ORDER BY resu.Agegroup, resu.Gender, resu.Event, resu.FullName, resu.BirthDate

Do you see how this works? You need an aggregate query that pulls the best times. Then you need to use the column values in that aggregate query in the ON clause to go get the details of the best times from the detail table.

If you want to report on just one event you can include an appropriate WHERE clause right before ORDER BY as follows.

 WHERE resu.Event = '50m Freestyle'
   AND resu.Gender = 'M'
   AND resu.Agegroup = 'Junior'

SQL Query sorting rows by duplicate name keeping lowest in result

2 Answers2