Re: Best way to do this query?

Cimode wrote:
>
> SELECT I.*
> FROM Invoices I
> INNER JOIN
> (SELECT customerid, max(invoiceid) invoiceid, MAX(date) most_recent_date
> FROM Invoices
> GROUP BY customerid
> ) AS C
> ON I.customerid = C.customerid and
> AND I.date = C.most_recent_date
>
> -->returns
> 3 John 2001-01-03 300
> 4 John 2001-01-03 500

This result is just I intended.
If there are more than two Invoices in a most recent day, list all of them.
I can't imagine good reason to choose one row from them. At least Marshall(Originator of this question) didn't mention it. Or he might not consider this situation(there are more than two Invoices in a day).

>
>
> -- Given the little information given by the questionner, the *safest*
> method forces a JOIN
> select A.* from Invoices A
> inner join
> (
> select customerid, max(invoiceid) invoiceid, max(date) as date1 from
> Invoices group by customerid
> ) B
> on A.invoiceid = B.invoiceid and
> A.date = B.date1
>
> --> I modified my statement as I realize I needed to add
> max(invoiceid).
>
> -->returns
> 4 John 2001-01-03 500
>
> Hope this helps...
Received on Wed Jan 17 2007 - 03:18:16 CET