Re: Best way to do this query?

Bob Badour wrote:

> 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!

Aye, but only based on an 'idiosyncracy' (feature?) of the distribution of mysql we had at the time and its handling of group by. It wouldn't give you the required results anywhere else, and we soon upgraded the system to be able to handle subqueries. Received on Wed Jan 17 2007 - 19:18:35 CET