[ILUG] MySQL question

[John Diamond]

that won't work
it max date grouped by customerid (few beers on :-)_
john
> > 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 :-( )
> >

>