[thelist] MySQL Grouping problem
Richard Livsey
richard at livsey.org
Mon Apr 11 14:02:25 CDT 2005
This looks so simple I must be overlooking something, but anyway...
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
Which groups by type fine, but doesn't order them by distance within the
grouping. IE the following
+------------+------------+------------+------------+
| id | name | distance | type |
+------------+------------+------------+------------+
| 2 | gregs | 150 | bakery |
+------------+------------+------------+------------+
| 1 | barclays | 400 | bank |
+------------+------------+------------+------------+
When I actually want:
+------------+------------+------------+------------+
| id | name | distance | type |
+------------+------------+------------+------------+
| 2 | gregs | 150 | bakery |
+------------+------------+------------+------------+
| 3 | halifax | 200 | bank |
+------------+------------+------------+------------+
How do I get it to order first before doing the grouping?
MySQL version is < 4.1 so no subqueries unfortunately.
Can anyone shed some light on this, or is it dead simple and I'm just
due a break?
Thanks in advance!
--
R.Livsey
www.livsey.org
More information about the thelist
mailing list