[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