Logo Questions Linux Laravel Mysql Ubuntu Git Menu

Select rows with column with min value

I need to select the rows with the minimum distance by grouping on the OrganisationID. Here is my data in a single table:

ID  OrganisationID    Distance
0        10             100
1        10             200
3        10             50
4        20             80
5        20             300

This is the result I want:

ID  OrganisationID    Distance
3        10             50
4        20             80
like image 473
Johann Avatar asked Mar 22 '23 10:03


1 Answers

This will accomplish that:

FROM yourTable t1
  LEFT JOIN yourTable t2
    ON (t1.OrganisationID = t2.OrganisationID AND t1.Distance > t2.Distance)
WHERE t2.OrganisationID IS NULL;

sqlfiddle demo

Note that if there are multiple rows with the lowest distance duplicate, this returns them both


If, as you say in the comments, only want one column and the MIN distance you can do it easily with MIN and GROUP BY:

SELECT city, MIN(distance)
FROM table2
GROUP BY city;

sqlfiddle demo

p.s. i saw your previous question that you deleted, and was answering it with a different thing than this (was going to tell you that since you had the organisationID in the WHERE clause, you could just do: SELECT TOP 1 ... order by Distance DESC), but if you need more it for more than one organisationID, this is something that can get you there)

like image 193
Filipe Silva Avatar answered Mar 24 '23 04:03

Filipe Silva