Re: Best way to do this query?

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:1168805451.817253.183270_at_m58g2000cwm.googlegroups.com...
> 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?
>
>
> Marshall
>

If I've understood real nature of your question, it can be generalized as follows:

How do I get attributes not included in the group by clause, after using a group by clause in order to aggregate and summarize results.

I have an answer, albeit an unsatisfying one:

You use a correlated subquery in the where clause to pick out the row that matches the one having the quality you looked for (like max(date)). The subquery has a group by, the main query does not.

If all this makes no sense, I'll try to come up with an example using invoices. I'm too lazy right now. Received on Sun Jan 14 2007 - 21:27:07 CET