May 1, 2015 at 7:43 am
I have a table with items that are versioned.
The version numbers are stored as varchar with three parts in the format: MajorNumber.MiddleNumber.MinorNumber
I am looking for an elegant way to find the latest/max version of each item.
Here is a sample table and data:
DECLARE @Items TABLE
(
ItemIDINT
,[Version]VARCHAR(10)
)
INSERT INTO @Items (ItemID, [Version])
SELECT 1, '1.0.0'
UNION ALL SELECT 1, '1.1.0'
UNION ALL SELECT 1, '10.0.0'
UNION ALL SELECT 1, '2.0.0'
UNION ALL SELECT 1, '11.0.0'
UNION ALL SELECT 2, '1.0.0'
UNION ALL SELECT 2, '1.0.1'
UNION ALL SELECT 2, '1.1.0'
UNION ALL SELECT 3, '7.14.0'
UNION ALL SELECT 3, '8.2.0'
UNION ALL SELECT 3, '8.4.0'
UNION ALL SELECT 3, '8.4.2'
Desired Results:
ItemID | Version
-------------------
1 | 11.0.0
2 | 1.1.0
3 | 8.4.2
May 1, 2015 at 7:57 am
WITH CTE AS (
SELECT ItemID, [Version],
ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY CAST(PARSENAME([Version],3) AS INT) DESC,CAST(PARSENAME([Version],2) AS INT) DESC,CAST(PARSENAME([Version],1) AS INT) DESC) AS rn
FROM @Items)
SELECT ItemID, [Version]
FROM CTE
WHERE rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537May 1, 2015 at 8:03 am
This is perfect! Thank you Mark!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy