[ILUG] MySQL question

[Matthew French]

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 @yahoo.com address at http://mail.yahoo.com