From: John Diamond (diamondj at domain indigo.ie)
Date: Sat 01 Sep 2001 - 18:02:07 IST
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 :-( )
> >
>
This archive was generated by hypermail 2.1.6 : Thu 06 Feb 2003 - 13:11:56 GMT