[thelist] MySQL Grouping problem

Richard Livsey richard at livsey.org
Mon Apr 11 14:41:15 CDT 2005


Jay Blanchard wrote:

>[snip]
>
>I have a table called 'places' with a list of places, their distance 
>from some point and a type.
>
>Eg:
>
>+------------+------------+------------+------------+
>| id         | name       | distance   | type       |
>+------------+------------+------------+------------+
>| 1          | barclays   | 400        | bank       |
>+------------+------------+------------+------------+
>| 2          | gregs      | 150        | bakery     |
>+------------+------------+------------+------------+
>| 3          | halifax    | 200        | bank       |
>+------------+------------+------------+------------+
>| 4          | hampsons   | 300        | bakery     |
>+------------+------------+------------+------------+
>
>How do I get a list of the places nearest to the point, limited by type?
>
>I've tried:
>
>SELECT *
>FROM places
>GROUP BY type
>ORDER BY distance ASC
>
>[/snip]
>
>Try MIN(distance) in the select clause
>  
>
That gets the min distance ok, but doesnt get the rest of the fields 
correctly still.

SELECT * , MIN( distance ) AS minDist
FROM places
GROUP BY TYPE
ORDER BY minDist ASC

IE:

+------+---------+---------+---------+--------+
| id   | name    | distance| type    | minDist|
+------+---------+---------+---------+--------+
| 2    | gregs   | 150     | bakery  | 150    |
+------+---------+---------+---------+--------+
| 1    | barclays| 400     | bank    | 200    |
+------+---------+---------+---------+--------+

-- 
R.Livsey
www.livsey.org



More information about the thelist mailing list