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 stackoverflow.com/questions/11683712/sql-group-by-and-min — Yaroslav
– Yaroslav, Commented Jul 27, 2012 at 8:32
really ? select code, distance from locations; showed that output ? — Nagri
– Nagri, Commented Jul 27, 2012 at 8:33

Joe G Joseph · Accepted Answer · 2012-07-27 08:46:44Z

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, 2012 at 8:46

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ashwinsakthi · Accepted Answer · 2012-07-27 08:49:02Z

0

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

edited Jul 27, 2012 at 8:49

user647772

answered Jul 27, 2012 at 8:31

ashwinsakthi

1,9564 gold badges30 silver badges56 bronze badges

2 Comments

cdub Over a year ago

what if 2 codes have the exact same location, as i need all codes too?

Madhivanan Over a year ago

This will lead to error. You need to include distinace in group by clause which will give different result

Michelle B · Accepted Answer · 2012-07-27 10:35:18Z

0

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

answered Jul 27, 2012 at 10:35

Michelle B

241 bronze badge

1 Comment

cdub Over a year ago

nope this doesn't work, you can still get locations that don't match the min distance because of the group by

Collectives™ on Stack Overflow

SQL Group By and Min in MySQL

3 Answers 3

Comments

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Linked

Related