From: Matthew French (mfrench42 at domain yahoo.co.uk)
Date: Sat 01 Sep 2001 - 22:52:25 IST
On Sat, Sep 01, 2001 at 03:36:11PM +0100, Niall O Broin wrote:
> Given two tables
<snip>
> 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 :-( )
Hmmm. Okay I am not too familiar with MySQL but a quick browse through
the internet and it seems as if it supports standard SQL.
Which means that you will have problems with the query you are trying
to run. The reason is that you have not joined the two tables and will
get a whole load of seemingly random data back. (Or more accurately
the cross product.) Assuming of course you have not left out the join
for brevity.
To do what you require, I would suggest trying the following
query:
SELECT c.Name,mostrecent=MAX(d.Date)
FROM Customers c, Deliveries d
WHERE c.ID=d.Customer_ID
GROUP BY c.Name
ORDER BY MAX(d.Date)
I have not tested this but it should work on most SQL compliant
databases. I would suggest dropping the ORDER BY clause until you have
a working query as these can be touchy when grouping.
Let me know if it works.
HTH,
- Matthew
_________________________________________________________
Do You Yahoo!?
Get your free at domain yahoo.com address at http://mail.yahoo.com
This archive was generated by hypermail 2.1.6 : Thu 06 Feb 2003 - 13:11:56 GMT