April 30, 2009 at 3:16 am
Hi gurus,
My table is as below
Item Cost Date
GN00030 6.6716-Apr-09
GN00030 7.53-Sep-08
GN00044 2122-Apr-09
GN00044 2317-Dec-08
GN00044 224-Oct-08
I want to pick data for those items & cost which is most recent.That is in the above example it will be
GN00030 6.6716-Apr-09
GN00044 2122-Apr-09
Can some one help in this please.
April 30, 2009 at 3:50 am
Heard about ROW_NUMBER() function in 2005, that's what you require..
IF ( OBJECT_ID( 'tempdb..#Items' ) IS NOT NULL )
DROP TABLE #Items
CREATE TABLE #Items
(
ItemID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ItemName VARCHAR(10) NOT NULL,
ItemCost NUMERIC(5,2) NOT NULL,
ItemCostDate SMALLDATETIME NOT NULL
)
INSERT#Items( ItemName, ItemCost, ItemCostDate )
SELECT'GN00030', 6.67, '16-Apr-09'
UNION ALL
SELECT'GN00030', 7.5, '3-Sep-08'
UNION ALL
SELECT'GN00044', 21, '22-Apr-09'
UNION ALL
SELECT'GN00044', 23, '17-Dec-08'
UNION ALL
SELECT'GN00044', 22, '4-Oct-08'
; WITH ItemsCTE
AS
(
SELECTROW_NUMBER() OVER( PARTITION BY ItemName ORDER BY ItemCostDate DESC ) AS RowNum, *
FROM#Items
)
SELECTItemName, ItemCost, ItemCostDate
FROMItemsCTE
WHERERowNum = 1
--Ramesh
April 30, 2009 at 4:34 am
Isnt there another way?-Thanks
April 30, 2009 at 4:42 am
mathewspsimon (4/30/2009)
Isnt there another way?-Thanks
WHY?
Your in 2005 or in 2000?
ARUN SAS
April 30, 2009 at 4:44 am
the db is 2005 but we most of the things are still done as 2000.
April 30, 2009 at 4:47 am
Hi,
in your side OVER / PARTITION works or not?
ARUN SAS
April 30, 2009 at 4:49 am
No sir,It gives error.
April 30, 2009 at 5:01 am
mathewspsimon (4/30/2009)
Isnt there another way?-Thanks
There can be many other ways, though you should have posted in the correct forum.
This solution assumes that there is only 1 row available for an Item and for a date. If this is not the case then you have to dump this data in a sorted order into a temp. table with an identity column and do the join on the max. identity instead on the max. date column.
SELECTI1.ItemName, I1.ItemCost, I1.ItemCostDate
FROM#Items I1
INNER JOIN
(
SELECTItemName, MAX( ItemCostDate ) AS ItemCostDate
FROM#Items
GROUP BY ItemName
) I2 ON I1.ItemName = I2.ItemName AND I1.ItemCostDate = I2.ItemCostDate
--Ramesh
April 30, 2009 at 5:08 am
That worked.Thanks,Yes there is only 1 row for a given date.
April 30, 2009 at 5:13 am
Hi,
also try this
select a.ItemName,a.ItemCost,a.ItemCostDate from #Items a,
(SELECT distinct ItemName,max(ItemCostDate)ItemCostDate FROM #Items
group by ItemName)as b
where a.ItemName = b.ItemName
and a.ItemCostDate = b.ItemCostDate
ARUN SAS
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply