SQL Query vs aggregate MAX 1

[rcloutie]

Hi all,

I need to optimize a SQL query from a table counting about 1.6M records. Suppose a table which contains all products bought from a supplier (that is, PO detail lines).
I need to get the last price for each product.

Something like:
SELECT CodeProd, DescProd, Price, UnitPrice, MAX(DatePO)
FROM PODetail WHERE CodeSupp='QWERTY'
GROUP BY CodeProd, DescProd, Price, UnitPrice

The result is all price changes (because of the GROUP BY ...Price...) while I need only the last one.

Maybe using a subquery??? but keep in mind 1.6M records...

The same problem occurs if I want the equivalent for a customer (while table design is quite similar: all products sold vs last selling price - MAX(DateInvoice)

Any help would be great,

Thanks in advance,

Rej Cloutier

 

[bborissov]

SELECT PODetail.CodeProd,
       PODetail.DescProd,
       PODetail.Price,
       PODetail.UnitPrice,
       PODetail.DatePO
FROM PODetail
INNER JOIN (SELECT CodeProd, MAX(DatePO) AS DatePO
                   FROM PODetail
                   WHERE CodeSupp='QWERTY'
                   GROUP BY CodeProd) Tbl1
ON PODetail.CodeProd = Tbl1.CodeProd AND
   PODetail.DatePO   = Tbl1.DatePO
-- The query should work w/o following line
-- WHERE PODetail.CodeSupp='QWERTY'

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP