[thelist] MySQL Grouping problem

[Hassan Schroeder]

Richard Livsey wrote:

> I have a table called 'places' with a list of places, their distance 
> from some point and a type.

> +------------+------------+------------+------------+
> | 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?

If you know the number of distinct types in advance,

SELECT type, name, distance FROM places GROUP BY distance, type LIMIT 2;

:: where in this case '2' is the number of types, should work. If
you don't know in advance, you can first run:

SELECT DISTINCT type FROM places;

:: and plug that value into your LIMIT statement.

At least that seems to work on a simplistic, smallish sample. :-)
-- 
Hassan Schroeder ----------------------------- hassan at webtuitive.com
Webtuitive Design ===  (+1) 408-938-0567   === http://webtuitive.com

                           dream.  code.