[ILUG] MySQL question

[John Diamond]

----- Original Message -----
From: "Niall O Broin" <niall at linux.ie>
To: <ilug at linux.ie>
Sent: Saturday, September 01, 2001 3:36 PM
Subject: [ILUG] MySQL question


> Given two tables
>
>
> mysql> describe Customers;
> +--------------+-------------+------+-----+-----------+----------------+
> | Field        | Type        | Null | Key | Default   | Extra          |
> +--------------+-------------+------+-----+-----------+----------------+
> | Username     | varchar(32) |      | UNI |           |                |
> | Password     | varchar(32) | YES  |     | NULL      |                |
> | ID           | smallint(6) |      | PRI | NULL      | auto_increment |
> | Name         | varchar(50) |      |     |           |                |
> +--------------+-------------+------+-----+-----------+----------------+
> 9 rows in set (0.00 sec)
>
>  mysql> describe Deliveries;
>  +-------------+---------------+------+-----+------------+-------+
>  | Field       | Type          | Null | Key | Default    | Extra |
>  +-------------+---------------+------+-----+------------+-------+
>  | Customer_ID | smallint(6)   |      | PRI | 0          |       |
>  | Picture_ID  | int(11)       |      | PRI | 0          |       |
>  | Date        | date          |      | PRI | 0000-00-00 |       |
>  | When        | timestamp(14) | YES  |     | NULL       |       |
>  +-------------+---------------+------+-----+------------+-------+
>  4 rows in set (0.00 sec)
>
> How do I get a listing of Customers with the date of each customer's
latest
> delivery, ordered by the date ? I tried
>
> select Name, max(Date) as Max from Customers, Deliveries order by Max;

group by deliveries.date?




>
> but that tells me that
>
> Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is
> illegal if there is no GROUP BY clause

> so I tried various combinations of GROUP BY with no great success. Can I
do I
> do this and if so, how (and given that it's MySQL, I hope the answer
doesn't
> involve subselects :-( )
>
>
>
> Regards,
>
jd