[thelist] MySQL Grouping problem
Richard Livsey
richard at livsey.org
Mon Apr 11 16:09:01 CDT 2005
Matt Warden wrote:
>I really do not understand what you are trying to do. I'm surprised,
>actually, that your query is not throwing errors. Typically, the
>selected columns either need to be in the GROUP BY clause or have an
>aggregate function operating on it.
>
MySQL has many oddities, I guess this is one of them. Needless to say
the query runs fine.
>Could you post more than 2 rows of what you are desiring, so we can
>get a better idea of the ordering you want and how that relates to
>whatever you are trying to do with the grouping?
>
>I almost suspect that you are simply trying to order by distance and
>then by type (or the other way around). But, I can't tell.
>
>
Ok, here's some more data. I've got rid of the 'name' field as it's distracting and I can't be bothered to think of lots of bank names :o)
+------------+------------+------------+
| id | distance | type |
+------------+------------+------------+
| 1 | 400 | bank |
+------------+------------+------------+
| 2 | 150 | bakery |
+------------+------------+------------+
| 3 | 200 | bank |
+------------+------------+------------+
| 4 | 300 | pub |
+------------+------------+------------+
| 5 | 340 | tube stn |
+------------+------------+------------+
| 6 | 210 | pub |
+------------+------------+------------+
| 7 | 380 | bank |
+------------+------------+------------+
| 8 | 100 | bakery |
+------------+------------+------------+
| 9 | 220 | pub |
+------------+------------+------------+
| 10 | 100 | tube stn |
+------------+------------+------------+
Ok. So here we have 10 places near to a point.
There are 4 different types of place:
* bank
* tube station
* pub
* bakery
What I want to get, is one of each type of place which is nearest to the point.
I can find the nearest places, regardless of type, by the following:
SELECT * FROM places ORDER BY distance ASC
I can find the nearest bank by:
SELECT * FROM places WHERE type='bank' ORDER BY distance ASC LIMIT 1
(would return #3 as that's 200 away)
However, how to get the nearest of each individual type, without resorting to multiplpe queries, is puzzling me.
I'm beginning to think this is a non trivial problem and I may have to do some voodoo with temporary tables.
The ideal situation is 1 query which would return the following from the afformentioned dataset:
+------------+------------+------------+
| id | distance | type |
+------------+------------+------------+
| 8 | 100 | bakery |
+------------+------------+------------+
| 10 | 100 | tube stn |
+------------+------------+------------+
| 3 | 200 | bank |
+------------+------------+------------+
| 6 | 210 | pub |
+------------+------------+------------+
Thanks for the help anyway :o)
--
R.Livsey
www.livsey.org
More information about the thelist
mailing list