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 get only one code and the closest distance. So I want him to return this:

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

I initially had something like this:

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

Min worked fine if I didn’t want to get the correct location, which was due to the smallest distance. How to get the min (distance) and the correct location (depending on the order on the inserts in the table, sometimes you may end up in New York, but Charlotte is in the location).

Answer 1

To get the correct linked location, you will need to join the sub-select, which gets the minimum distance by one code, provided that the distance in the outer main table matches the minimum distance obtained in the subquery.

 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 

Answer 2

You can try to perform a nested search between the minimum grouping and the source table.

It looks like a trick

 SELECT MinPlaces.Code, MinPlaces.Distance, Places.Location FROM Places INNER JOIN ( SELECT Code, MIN(Distance) AS Distance FROM Places GROUP BY Code HAVING MIN(Distance) > 0 ) AS MinPlaces ON Places.Code = MinPlaces.Code AND Places.Distance = MinPlaces.Distance ORDER BY MinPlaces.Distance ASC 

UPDATE . Tested using the following:

 DECLARE @Places TABLE ( Code INT, Distance FLOAT, Location VARCHAR(50) ) INSERT INTO @Places (Code, Distance, Location) VALUES (106, 386.895834130068, 'New York, NY'), (80, 2116.6747774121, 'Washington, DC'), (80, 2117.61925131453, 'Alexandria, VA'), (106, 2563.46708627407, 'Charlotte, NC') SELECT MinPlaces.Code, MinPlaces.Distance, P.Location FROM @Places P INNER JOIN ( SELECT Code, MIN(Distance) AS Distance FROM @Places GROUP BY Code HAVING MIN(Distance) > 0 ) AS MinPlaces ON P.Code = MinPlaces.Code AND P.Distance = MinPlaces.Distance ORDER BY MinPlaces.Distance ASC 

And it gives:

Answer 3

You did not say your DBMS. The following solutions are for SQL Server.

 WITH D AS ( SELECT code, distance, location, Row_Number() OVER (PARTITION BY code ORDER BY distance) Seq FROM places ) SELECT * FROM D WHERE Seq = 1 

If you have a table with unique codes and an index in your "Places" table on [Code, distance], then the CROSS APPLY solution might be better:

 SELECT X.* FROM Codes C CROSS APPLY ( SELECT TOP 1 * FROM Places P WHERE C.Code = P.Code ORDER BY P.Distance ) X 

I cannot work long on a solution for mysql.

PS You cannot rely on the insertion order. Do not try!