Getting the latest values
-
Hi,
Sorry for the vague subject, I found difficult to describe my problem in a single line.
I have a simple table with the prices of some item along the time.
Each row contains an id for the item, the date when the price was recorded and the price of the item at that date.
Something like that :
create table prices(id int identity(1,1), item int, price float, date datetime)
insert into prices values (10, 12.34, '2005-12-01')
insert into prices values (10, 56.78, '2005-11-01')
insert into prices values (11, 0.57, '2005-09-01')
insert into prices values (11, 1, '2005-10-01')
I want to get only the latest price for each item. With my example, it would be the 1st and the 4th line.
I managed to do that with a function that uses a cursor.
My function :
create function LatestPrices()
returns @tbl table (id int, item int, price float, date datetime)
as
begin
declare curs cursor for select distinct item from prices
open curs
declare @sql varchar(8000)
declare @item int
fetch next from curs into @item
while @@fetch_status=0
begin
insert into @tbl select top 1 * from prices where item=@item order by date desc
fetch next from curs into @item
end
close curs
deallocate curs
return
end
select * from LatestPrices() gives
id item price date
-- ---- ----- ----------
1 10 12,34 2005-12-01
4 11 1 2005-10-01
This result is want I want, but I would like to get rid of the cursor, if that's possible.
Can you help me in finding a better simpler way ?
Thanks
Manu
-
Join to a derived table that gives you the MAX date per item:
Select Prices.*
From Prices
Inner Join
(
Select item, Max([Date]) As LastPriceDate
From Prices
Group By Item
) dtLatestPrice
On
Prices.Item = dtLatestPrice.Price And
Prices.[Date] = dtLatestPrice.LastPriceDate
-
I am sure PW meant to join by item and date
:Select Prices.*
From Prices
Inner Join
(
Select item, Max([Date]) As LastPriceDate
From Prices
Group By Item
) dtLatestPrice
On
Prices.Item = dtLatestPrice.Item And
Prices.[Date] = dtLatestPrice.LastPriceDate
* Noel -
Thank you PW, that's great !
(just replaced dtLatestPrice.Price by dtLatestPrice.Item)
-
Alrighty then. Apparently there is a caffeine deficiency problem on my end
Thanks for the correction.
-
I need to do the same type thing, but instead of getting the max date, I need the latest (top) 5 dates. Can this be done without using a cursor?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply