record retrieving problem

hi all,

I have a table productprice which has the following feilds

id price datecreated productname

1 12.00 13/05/2007 a1

2 23.00 14/05/2007 a1

3 24.00 15/05/2007 a1

4 56.00 13/05/2007 b1

5 34.00 18/05/2007 b1

6 23.00 21/05/2007 b1

7 11.00 12/02/2007 c1

8 78.00 12/03/2007 c2

Ineed to select the rows that are highlighted here.. ie the row that hasthe max(datecreated) for all the productname in the table..

plz help

thanks in advance..

I'll assume in row #8, the productname was supposed to be c1 and was a typo. With that assumption:

SELECT *

FROM (

SELECT *,row_number() OVER(Partition by productname ORDER BY datecreated DESC,id DESC) TheRank

FROM productprice

) t1

WHERE TheRank=1

|||

hi Motley,

First thanks to Motley for his reply..

row-number() function is a new funtion in sql swerver 2005..But Iam using sql server 2000.And I am Afraid that is funtion will work in sql server 2000.Sorry for not mentioning the databse that I am using..

I am not much aware of this function..I searched in the internet and I could find this result..

how can i do this in sql server 2000 ?

thanks in advance,

|||

SELECT p1.*

FROM ProductPrice p1

LEFT JOIN ProductPrice p2 ON ((p1.datecreated<p2.datecreated OR (p1.datecreated=p2.datecreated and p1.id<p2.id)) AND p1.productname=p2.productname)

WHERE p2.id IS NULL

Please test this, as I believe it's performance gets exponentially worse as the number of records grows.