Re: Best way to do this query?

From: Cimode <cimode_at_hotmail.com>
Date: 17 Jan 2007 02:40:22 -0800
Message-ID: <1169029571.634128.327420_at_m58g2000cwm.googlegroups.com>

Tonkuma wrote:
> 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).
I agree...The specification of the question are unsufficient to establish what would be an appropriate a correct solution...;) So any *solution* is merely an indication...nothing more nothing less...;) Received on Wed Jan 17 2007 - 11:40:22 CET