Re: Best way to do this query?
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 17 Jan 2007 15:24:17 GMT
Message-ID: <Bcrrh.1991$1x.32506_at_ursa-nb00s0.nbnet.nb.ca>
>
>
> 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:
>
> SELECT A.invoiceid, A.customerid, A.date, A.amount, Max(A.date -
> B.date)
> FROM invoices AS A
> INNER JOIN invoices AS B
> ON A.customerid = B.customerid
> GROUP BY A.customerid
Date: Wed, 17 Jan 2007 15:24:17 GMT
Message-ID: <Bcrrh.1991$1x.32506_at_ursa-nb00s0.nbnet.nb.ca>
JOG wrote:
> 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:
>
> SELECT A.invoiceid, A.customerid, A.date, A.amount, Max(A.date -
> B.date)
> FROM invoices AS A
> INNER JOIN invoices AS B
> ON A.customerid = B.customerid
> GROUP BY A.customerid
And that compiled? Egad! Received on Wed Jan 17 2007 - 16:24:17 CET