Re: Best way to do this query?

On Jan 14, 12:33 pm, Bob Badour <bbad..._at_pei.sympatico.ca> 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?
> See "quota query"
>
> Your stated problem is a top 1 query, which is very similar to a top 10
> query only with fewer tuples.

Hmmm. Are you saying I'd to a top-1 query for each customer, and then join to get the result? Alternatively, your post made me consider the idea of a group-by aspect to quota queries, which I hadn't considered
before.

Also, I imagine making up some fancy aggregate function to do something similar.

Marshall Received on Sun Jan 14 2007 - 21:42:41 CET