From: Niall O Broin (niall at domain linux.ie)
Date: Sat 01 Sep 2001 - 15:33:06 IST
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 :-( )
Regards,
Niall
This archive was generated by hypermail 2.1.6 : Thu 06 Feb 2003 - 13:11:56 GMT