Re: Best way to do this query?
Date: 17 Jan 2007 06:16:30 -0800
Message-ID: <1169043390.609432.266310_at_38g2000cwa.googlegroups.com>
Marshall wrote:
> I run in to this kind of query every so often. I wonder if
> there's a canonical best way to do it?
>
> Consider the following schema:
>
> Invoices:{invoiceid, customerid, date, amount}
>
> Each invoice has an id, the id of the customer who opened the
> invoice, the date of the creation of the invoice, and the amount.
>
> If I want to know the date of every customer's most recent invoice,
> that's easy:
>
> select customerid, max(date) from Invoices group by customerid
>
> But suppose I also want to know the id and amount of that particular
> invoice?
Just as an aside, I was forced to use MySQL 4 for a small project and
had a similar query requirement. For those who haven't had the dubious
pleasure of using MySQL 4, it's handling of sub queries is tenuous to
non-existent, so the approach suggested by Cimode wasn't an option.
Hence I ended up with a slightly different approach as follows:
ON A.customerid = B.customerid
GROUP BY A.customerid
Regards, Jim.
>
>
> Marshall
Received on Wed Jan 17 2007 - 15:16:30 CET