Re: Best way to do this query?

Marshall schreef:
> 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:

There could be more invoices with the same date.

>
> select customerid, max(date) from Invoices group by customerid
>
> But suppose I also want to know the id and amount of that particular
> invoice?
>
>
> Marshall

I would not call this a 'canonical' way but....

select inv.invoiceid, inv.customerid, inv.amount, max(inv.date) from Invoices as inv
group by inv.invoiceid, inv.customerid, inv.amount having max(inv.date) = (select max(date) from Invoices where customerid = inv.customerid)
order by inv.customerid

Carlos Received on Wed Jan 17 2007 - 09:37:15 CST