Wednesday, 14 August 2013

TSQL Selecting Highest RowNumber record with distinct field value?

TSQL Selecting Highest RowNumber record with distinct field value?

Lets say I have a table for my parts that looks like so:
PartNumber | Cost | Revision
aaaaa | 10 | Rev1
xxxxx | 12 | Rev1
aaaaa | 10 | Rev2
ccccc | 37 | Rev1
ttttt | 23 | PreRelease
sssss | 19 | Rev7
ttttt | 11 | Rev0
I want to somehow get distinct part number / cost records WITH the latest
revision. I know the latest revision will always be the last entry in the
table for a given part. For example, here, "aaaaa"s latest rev is "Rev2"
which is the last record in the table matching that part number. If a new
rev was entered it would come at the end. This is what I would like the
result to look like.
PartNumber | Cost | Revision
xxxxx | 12 | Rev1
aaaaa | 10 | Rev2
ccccc | 37 | Rev1
sssss | 19 | Rev7
ttttt | 11 | Rev0
I realize I can GROUP BY PartNumber, Cost to get the distinct records but
how do I get the latest rev with that? Thanks in advance.

No comments:

Post a Comment