SQL Puzzle | The Product’s Latest Version Puzzle

In this puzzle you have to return each product with their latest version. Please check out the sample input values and sample expected output below.
For details please check out the sample input and the expected output below.

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data

--

CREATE TABLE VersionHistory 
( 
        Product VARCHAR(10)
       ,Versions VARCHAR(30)
)
GO


insert into VersionHistory values ('a','1.1')
insert into VersionHistory values ('a','1.1.1')
insert into VersionHistory values ('a','1.2.10')
insert into VersionHistory values ('a','1')
insert into VersionHistory values ('a','1.10.1')
insert into VersionHistory values ('a','1.1.2')
insert into VersionHistory values ('b','2.1')
insert into VersionHistory values ('b','2')
insert into VersionHistory values ('a','1.10.2')
insert into VersionHistory values ('a','1.2')
insert into VersionHistory values ('a','1.2.4')
insert into VersionHistory values ('a','1.2.5')
GO


--

—

SOLUTION – 1

--

/*
**  Solution
*/

SELECT Product, Versions
FROM
(
       SELECT 
           Product
          ,Versions
          ,ROW_NUMBER() OVER (PARTITION BY Product ORDER BY CAST('/'+REPLACE(Versions,'.','/')+'/' AS HIERARCHYID) DESC) AS rnk 
       FROM VersionHistory
)T
WHERE T.rnk = 1

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com