SQL Group By and min (MySQL)
13,030
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
Author by
cdub
Updated on June 24, 2022Comments
-
cdub almost 2 years
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).