Select rows with column with min value

Question

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

Solution

This will accomplish that:

SELECT t1.*
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

---

EDIT:

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)

Comments

[Johann]

So it's not possible to do this with the Group By clause and MIN function?

[Raphaël Althaus]

@AndroidDev not directly if you need the Id.

[Filipe Silva]

@AndroidDev. What Raphael said :)

[Johann]

Actually, I have a 4th column called City. I don't really need the ID. Is it possible to return just the Distance and City? I don't even need to return the OrganisationID.

[Filipe Silva]

@AndroidDev. if you only need city and MIN(distance) you can do the MIN(distance) and GROUP BY city. This kind of tricks (mine or Raphael's) is to get more than one column besides the aggregated column. If you only need one, you can do the MIN and group by

[Johann]

The problem with Group By City is that I can have two different cities belonging to the same organistion and this will result in both cities being returned. I only want the one that has the smallest distance.

[Filipe Silva]

@AndroidDev. Yes. That is true. You gotta use the workaround then.