Selecting a different field from a group.

[h0ffman]

Hi

Hope you can help, been racking my brains on this.

How can I pick a field from a group, based on the order of another field in the group? Not sure if that explains it so..

I have a table

RowID (Int, ID)
ProductCode (Varchar)
CreatedDate (DateTime)

Basically, I want the RowID based on the most recent CreatedDate within each ProductCode.

So, I can do this..

SELECT ProductCode, MAX(CreateDate)
FROM Table
GROUP BY ProductCode

That shows me the highest date on each product, but I want to know what the RowID of that record is?

How can it be done??

Please help

[galexyus]

Will this work?

SELECT * FROM Table t1
WHERE CreateDate = (SELECT MAX(CreateDate) FROM Table t2 WHERE t1.ProductCode = t2.ProductCode)
ORDER BY ProductCode