How to select multiple latest records with different latest dates

Question

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Please review the article "Please provide DDL and sample data" at

As a guess:

Create table ItemPrices

(ItemName varchar(255) not null

,EffectiveDate datetime not null

,PriceAmt numeric(8,2) not null

,constraint ItemPrices_PK primary key (ItemName , EffectiveDate )

)

go

select ItemPrices.*

from ItemPrices

join (select ItemName

, MAX(EffectiveDate ) as HighestEffectiveDate

from ItemPrices

group by ItemName

) as ItemPrices_Lastest

on ItemPrices_Lastest.ItemName = ItemPrices.ItemName

and ItemPrices_Lastest.HighestEffectiveDate = ItemPrices.EffectiveDate

SQL = Scarcely Qualifies as a Language

poztme Grasshopper Points: 15 More actions · Answer 2

Excellent. It worked for me.

Thanks a lot for your kind help