Re: Best way to do this query?

Tonkuma wrote:
> 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

Actually, it really depends onto *how* unicity is implemented. If unicity is implemented as a constraint onto (customerid+date) then your statement is fine. But we don't know for sure from the question asked if it as actually the case. In most cases, unicity is implemented on Invoiceid being itself the implementation (usually a counter) of some surrogate key. In the case of the following dataset (no unicity implemented onto (customerid + name)) , you would get redundant data returned using your statement. Which is why it is interesting to use invoiceid to get rid of all possible redundancy in the upper date data returned. Here is the proof...

create view view1 as
select 1 as Invoiceid, 'John' as customerid, '2001-01-01' as date, 500 as amount
union all
select 2 as Invoiceid, 'John' as customerid, '2001-01-02' as date, 500 as amount
union all
select 3 as Invoiceid, 'John' as customerid, '2001-01-03' as date, 300 as amount
union all
select 4 as Invoiceid, 'John' as customerid, '2001-01-03' as date, 500 as amount
go
--create Invoices table
select * into Invoices from dbo.view1

• doing a JOIN using your method

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

• Given the little information given by the questionner, the *safest* method forces a JOIN select A.* from Invoices A inner join ( select customerid, max(invoiceid) invoiceid, max(date) as date1 from Invoices group by customerid ) B on A.invoiceid = B.invoiceid and A.date = B.date1

--> I modified my statement as I realize I needed to add max(invoiceid).

-->returns
4 John 2001-01-03 500

Hope this helps... Received on Tue Jan 16 2007 - 10:39:30 CET