From: Niall O Broin (niall at domain linux.ie)
Date: Sun 02 Sep 2001 - 13:12:12 IST
Answering my own question for the second time in a week is bad, but my
answer wasn't right, so further clarification was needed. I asked
> 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;
>
> 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 :-( )
I get an answer from (I though had tried this before my first question)
select ID, Name, max(Date) as max from Customers, Deliveries where
ID=Customer_ID group by ID order by max;
(I group by ID because ID is unique - Name is not)
but what that returns me is not correct - I get 85 rows returned, yet
select count(distinct(Customer_ID)) from Deliveries;
returned 91. There's something decidedly strange there - that group query
should surely return 91 rows. A little (no, a lot) of investigation revealed
that I had some Delivery records for deleted Customers, although the Perl
code which deletes a Customer also deletes all associated Delivery records,
or should :-), and the log doesn't show any errors. Ah well, this whole
thing has led to a cleanup of the data, which can't be all bad. When is
MySQL getting triggers :-) ?
Thanks to John Diamond who through his celebratory beer haze pointed out that
> it max date grouped by customerid
and later pointed out that I'd better group by ID as Name may not be unique
(it wasn't (customer requirement), and that bit me for a while). Of course
to add to the fun :-) I did't want 91 rows returned - I wanted 179 i.e.
one for each customer, even those which don't have any deliveries recorded.
A little RTFM led me to the conclusion that a LEFT JOIN is what I needed so
I came up with
select ID, Name, max(Date) as max from Customers left join Deliveries
on ID=Customer_ID group by ID order by max
which does the business. It's definitely time to pick up that SQL book again
and try to read the bloody thing :-) esp. WRT joins. For instance, after
solving my problem I got curious and I tried all of these
select ID, Name, max(Date) as max from Customers inner join Deliveries on
ID=Customer_ID group by ID order by max,ID;
select ID, Name, max(Date) as max from Customers natural join Deliveries
where ID=Customer_ID group by ID order by max,ID;
select ID, Name, max(Date) as max from Customers, Deliveries where
ID=Customer_ID group by ID order by max,ID;
all of which give me the exact same output. Why do I sometimes need an ON
clause, and sometimes WHERE will do ? Oh bugger, back to that book :-( This
is obviously why good DBAs command such high rates :-)
Niall
This archive was generated by hypermail 2.1.6 : Thu 06 Feb 2003 - 13:11:56 GMT