Re: Best way to do this query?

Cimode wrote:
> Marshall wrote:
>
> > But suppose I also want to know the id and amount of that particular
> > invoice?
> Something like this should do...
>
> select * from Invoices A
> inner join
> (
> select invoiceid, max(date) as date1 from Invoices group by customerid
> ) B
> on A.invoiceid = B.invoiceid and
> A.date = B.date1
>
> Hope this helps...
Cimode, you should use customerid instead of invoiceid for JOIN condition.
SELECT I.*
  FROM Invoices I

       INNER JOIN
       (SELECT customerid, MAX(date) most_recent_date
          FROM Invoices
         GROUP BY customerid
       ) AS C
         ON  I.customerid = C.customerid
         AND I.date = C.most_recent_date