SQL Group By and min (MySQL)

Question

I have the following SQL:

select code, distance from places;    

The output is below:

CODE    DISTANCE            LOCATION
106     386.895834130068    New York, NY
80      2116.6747774121     Washington, DC
80      2117.61925131453    Alexandria, VA
106     2563.46708627407    Charlotte, NC

I want to be able to just get a single code and the closest distance. So I want it to return this:

CODE    DISTANCE            LOCATION
106     386.895834130068    New York, NY
80      2116.6747774121     Washington, DC

I originally had something like this:

SELECT code, min(distance), location
GROUP BY code
HAVING distance > 0 
ORDER BY distance ASC

The min worked fine if I didn't want to get the correct location that was associated with the least distance. How do I get the min(distance) and the correct location (depending on the ordering on the inserts in the table, sometimes you could end up with the New York distance but the Charlotte in Location).

Answer 1

To get the correct associated location, you'll need to join a subselect which gets the minimum distance per code on the condition that the distance in the outer main table matches with the minimum distance derived in the subselect.

SELECT a.code, a.distance
FROM   places a
INNER JOIN
(
    SELECT   code, MIN(distance) AS mindistance
    FROM     places
    GROUP BY code
) b ON a.code = b.code AND a.distance = b.mindistance
ORDER BY a.distance