Max Version Number

• Goldie Lesser

  SSCertifiable

  Points: 6155

  More actions

  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

• Mark Cowne

  One Orange Chip

  Points: 26981

  More actions

  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

• Goldie Lesser

  SSCertifiable

  Points: 6155

  More actions

  May 1, 2015 at 8:03 am

  #1794991

  This is perfect! Thank you Mark!