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 ...

 

[blom0344]

SELECT T.STORE_ID,T.ITEM_ID,T.PRICE,T.DATE  FROM TABLE T WHERE
T.DATE = (SELECT MAX(V.DATE) FROM TABLE V WHERE 
T.STORE_ID = V.STORE_ID AND
T.ITEM_ID = V.ITEM_ID AND
T.PRICE = V.PRICE)

Ties Blom

 

[blom0344]

Sorry, should be:

SELECT T.STORE_ID,T.ITEM_ID,T.PRICE,T.DATE  FROM TABLE T WHERE
T.DATE = (SELECT MAX(V.DATE) FROM TABLE V WHERE 
T.STORE_ID = V.STORE_ID AND
T.ITEM_ID = V.ITEM_ID)

Well, I think.

Alternative is to use inline view with rank() function which is a bit more intuitive

Ties Blom

 

[Mercury2]

Select Store_id, Item_ID, Price, Max(Date) 
From Table
Group by Store_id, Item_ID, Price

should do the trick (not tested).

 

[blom0344]

Nope, that would be a little too easy.
Grouping should be only by store and item.
In mercury's example one would again fetch every record from the set..

Ties Blom

 

[Mercury2]

Alright Ties, my bad. I was too fast ...