468,106 Members | 1,367 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,106 developers. It's quick & easy.

Selecting a different field from a group.

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
Oct 4 '06 #1
1 1188
Will this work?

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM Table t1
  2. WHERE CreateDate = (SELECT MAX(CreateDate) FROM Table t2 WHERE t1.ProductCode = t2.ProductCode)
  3. ORDER BY ProductCode
Oct 5 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by manning_news | last post: by
6 posts views Thread by aaj | last post: by
5 posts views Thread by =?Utf-8?B?U3RldmUgSGFwcA==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.