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

Answer 1

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)