SQL Problem

[Maiden1974]

Hi,

I'd appreciate it if anyone could help me with the below ..

Table

Store_id Item_ID Price Date
------ ------- ----- ------
00001 001 9.99 01/11/2006
00001 001 8.50 09/05/2005
00001 002 9.50 11/11/2005
00001 002 7.99 02/12/2006
00002 001 6.99 01/01/2006
00002 001 5.50 12/12/2006
00002 002 8.99 03/04/2005
00002 002 7.99 02/12/2006

I'm trying to get the following output...the Last Price grouped by Store and Item.

Store_id Item_ID Price Date
------ ------- ----- ------
00001 001 9.99 01/11/2006
00001 002 8.50 02/12/2006
00002 001 6.50 12/12/2006
00002 002 7.99 02/12/2006

Thankyou ...

 

[PaulBricker]

You may be able to do this in one query, but I did it in two. The first one

SELECT tblA.Store_Id, tblA.Item_Id, Max(tblA.PriceDate) AS MaxOfPriceDate
FROM tblA
GROUP BY tblA.Store_Id, tblA.Item_Id;

I saved that as qryA
The second query

SELECT qryA.Store_Id, qryA.Item_Id, qryA.MaxOfPriceDate, tblA.Price
FROM tblA INNER JOIN qryA ON (tblA.PriceDate = qryA.MaxOfPriceDate) AND (tblA.Item_Id = qryA.Item_Id) AND (tblA.Store_Id = qryA.Store_Id);

This will get you there.

Paul

 

[Golom]

Try

Select Store_Id, Item_Id, Price, [Date]

From myTable T

Where [Date] = (Select MAX([Date]) From myTable S
                Where S.Store_Id = T.Store_Id 
                  AND S.Item_Id  = T.Item_Id)