[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