[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