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 ```

Accepted Answer

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)

Select rows with column with min value

Solution

Problem