SQL Group By and Min in MySQL

Question

I have the following SQL in MySQL DB:

select code, distance from locations;

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
106     2030.5845606766     Atalanta, GA

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

Repeated http://stackoverflow.com/questions/11683712/sql-group-by-and-min — Yaroslav, Jul 27 '12 at 08:32
really ? select code, distance from locations; showed that output ? — Nagri, Jul 27 '12 at 08:33

score 0 · Answer 1 · edited Jul 27 '12 at 08:49

0

  SELECT code, min(distance), location
GROUP BY code,location
  HAVING distance =min(distance)

edited Jul 27 '12 at 08:49

answered Jul 27 '12 at 08:31

ashwinsakthi

1,856
4
27
56

what if 2 codes have the exact same location, as i need all codes too? – cdub Jul 27 '12 at 08:33
This will lead to error. You need to include distinace in group by clause which will give different result – Madhivanan Jul 27 '12 at 08:40

score 0 · Answer 2 · answered Jul 27 '12 at 08:46

0

try this:

select L.* from (
    SELECT code, min(distance) as min_distance 
    from places
    GROUP BY code)a
join places L
on L.CODE=a.CODE
and L.DISTANCE=a.min_distance

answered Jul 27 '12 at 08:46

Joe G Joseph

23,518
5
56
58

score 0 · Answer 3 · answered Jul 27 '12 at 10:35

0

SELECT code, distance, location FROM locations GROUP BY code HAVING distance > 0 ORDER BY distance ASC LIMIT 1

answered Jul 27 '12 at 10:35

Michelle B

24
1

nope this doesn't work, you can still get locations that don't match the min distance because of the group by – cdub Jul 27 '12 at 22:08

SQL Group By and Min in MySQL

3 Answers3