Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Sort Data by Version Puzzle

Puzzle Statement

In this puzzle, we have column a called version history. In this the version of an application are stored. Now what is a version number ? The assembly version number is part of an assembly’s identity and plays a key part in binding to the assembly and in version policy. For Details please visit – https://msdn.microsoft.com/en-us/library/system.reflection.assemblyversionattribute(v=vs.71).aspx. The version number has four parts, as follows:

Major version.Minor version.Build number.Revision

The puzzle is we have sort this version column from starting version to the latest version.

Sample Input

VersionHistory
1.1
1.1.1
1.2.10
1
1.10.1
1.1.2
2.1
2
1.10.2
1.2
1.2.4
1.2.5

Expected output

VersionHistory
1
1.1
1.1.1
1.1.2
1.2
1.2.4
1.2.5
1.2.10
1.10.1
1.10.2
2
2.1

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

--

CREATE TABLE VersionHistory
(
	VersionHistory VARCHAR(10)
)
GO

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

GO

--

SOLUTION 1 | USING XML, CROSS APPLY & Pivot

--

;WITH CTE AS 
(
	SELECT VersionHistory, CAST(CONCAT('<A>',REPLACE(VersionHistory,'.','</A><A>'),'</A>') AS XML) Xmlcol 
	, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk 
	FROM VersionHistory
)
,CTE1 AS 
(
	SELECT VersionHistory, SplittedString , rnk , ROW_NUMBER() OVER (PARTITION BY RNK ORDER BY (SELECT NULL)) finalrnk 
	FROM 
		(SELECT * FROM CTE) s
	CROSS APPLY
		(
			SELECT ProjectData.D.value('.', 'SMALLINT') as SplittedString
			FROM s.xmlcol.nodes('A') as ProjectData(D)
		) a
)
SELECT VersionHistory FROM CTE1
PIVOT ( MAX(SplittedString) FOR finalrnk IN ([1],[2],[3]) ) pvt
ORDER BY [1],[2],[3]

--

SOLUTION 2 | USING HierarchyID DataType 

--

SELECT VersionHistory FROM VersionHistory
ORDER BY CAST('/'+REPLACE(VersionHistory,'.','/')+'/' AS HIERARCHYID)

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com