This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input & output data, and sort product data based on its versions information.
Input data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
ProductName ProductVersion ----------- -------------------- ABC 1.2.3 EEF 1.1.2.2 PQR 4.3 ABC 2.3 PQR 2.2.6.7 EEF 0.2.3.4 YRT 0.1.1 ABC 0.8.7.1 ABC 0.1.1.2 EEF 0.8.7.2 PQR 3.6.5.1 ABC 8.6.1.2 EEF 3.6.5.2 PQR 5.4.2.1 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
ProductName ProductVersion ----------- -------------------- ABC 0.1.1.2 ABC 0.8.7.1 ABC 1.2.3 ABC 2.3 ABC 8.6.1.2 EEF 0.2.3.4 EEF 0.8.7.2 EEF 1.1.2.2 EEF 3.6.5.2 PQR 2.2.6.7 PQR 3.6.5.1 PQR 4.3 PQR 5.4.2.1 YRT 0.1.1 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE tbl_ProductVersions ( ProductName VARCHAR(10) ,ProductVersion VARCHAR(20) ) GO INSERT INTO tbl_ProductVersions VALUES ('ABC', '1.2.3'),('EEF','1.1.2.2') ,('PQR', '4.3'),('ABC','2.3') ,('PQR', '2.2.6.7'),('EEF','0.2.3.4') ,('YRT', '0.1.1'),('ABC','0.8.7.1') ,('ABC', '0.1.1.2'),('EEF','0.8.7.2') ,('PQR', '3.6.5.1'),('ABC','8.6.1.2') ,('EEF', '3.6.5.2'),('PQR','5.4.2.1') GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
WITH CTE AS ( SELECT ProductName ,ProductVersion ,ROW_NUMBER() OVER (PARTITION BY ProductName ORDER BY CAST('/'+REPLACE(ProductVersion,'.','/')+'/' AS HIERARCHYID)) as Rno FROM tbl_ProductVersions ) SELECT ProductName ,ProductVersion FROM CTE ORDER BY ProductName,Rno |
