Post reply

Max Version Number

  • May 1, 2015 at 7:43 am

    #297759

    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

    #1794987

    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/61537

  • May 1, 2015 at 8:03 am

    #1794991

    This is perfect! Thank you Mark!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply