CODEWITHSUNDEEP
t-sql
BBauer42
BBauer42

Reputation: 3657

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.

Upvotes: 1

Views: 3094

Answers (3)

mortb
mortb

Reputation: 9849

You can for example do an inline query like this:

SELECT DISTINCT 
PartNumber, 
Cost, 
(
     SELECT MAX(Revision) 
     FROM parts t2 
     WHERE t1.PartNumber = t2.PartNumber
)
FROM parts t1

SQLFiddle

Hope this helps!

Upvotes: 0

valex
valex

Reputation: 24144

To get a LAST ENTERED row you need at least a primary key ID field in this table:

Here is a query:

SQLFiddle demo

SELECT PartNumber,Cost,Revision 
FROM
(SELECT PartNumber,Cost,Revision,
       ROW_NUMBER() OVER (PARTITION BY PartNumber ORDER BY ID DESC) as rn
       FROM T
) AS T1
WHERE RN=1

If you have no ID field try to replace ORDER BY ID DESC to ORDER BY Revision DESC. But in this case your REVISION field should be formatted to make this order right.

Upvotes: 3

ganders
ganders

Reputation: 7433

Throw the Group By query in a subquery, then from there, grab the records that make it distinct, without the primary key, then take the max primary key from there.

Looks something like this, I'll work on converting it to your data...

Select Max(p2.PersonID), p2.FirstName
From core.Person p2
Where Exists
(
    Select p.FirstName, Count(1)
    From core.Person p
    Where p.FirstName = p2.FirstName
    Group By p.FirstName
)
Group By p2.FirstName

Upvotes: 0

Related Questions