Ordering the data by a column which has numbering/indexation type data with multiple decimals

Question

I have a table which has a column called 'clause' with indexation type data such as 1.,1.0, 1.1.1., 1.1.2., 1.10., 1.2., 2., 2.1.1., 3. etc...

In my query, I need to select this data by order by 'clause' column. Column data type can be anything but for now it is nvarchar type. When I run my query -

1.
1.0
1.1.1.
1.1.2.
1.10.
1.2.
2.
2.1.1.
3.

I understand why this is happening but I want to achieve the following result where 1.2 comes before 1.10. Reason being 2 smaller than 10. So I need the following result.

1.
1.0
1.1.1.
1.1.2.
1.2.
1.10.
2.
2.1.1.
3.

Please can you expert advice if this is possible in SQL and how ?

Thanks,

Thom A · Answer 1 · 2022-07-26T10:55:51.873

4

This answer is inspired by this answer from Steve Kass. This difference, however, is that you have trailing .'s in some places. As a result I TRIM (I assume you are using a fully supported version of SQL Server as you don't state otherwise) these from the value first. I also switch to TRY_CONVERT (you can use TRY_CAST if you prefer) to avoid errors on values that are completely nonsense as well.

SELECT *
FROM (VALUES ('1.'),
             ('1.0'),
             ('1.1.1.'),
             ('1.1.2.'),
             ('1.10.'),
             ('1.2.'),
             ('2.'),
             ('2.1.1.'),
             ('3.')) V (V)
ORDER BY TRY_CONVERT(hierarchyid,'/' + REPLACE(TRIM('.' FROM V.V), '.', '/') + '/');

edited Jul 26 '22 at 10:55

answered Jul 26 '22 at 10:50

Thom A

88,727
11
45
75

This works and it seems like end of the tunnel but some of the values can have alpha characters at the end e.g. ```2.1.1.b.```. When I use them with your query, these values end up at the top and it breaks the hierarchy. Any fix for that ? I have edited my question to reflect that... – tanatan Jul 26 '22 at 11:30
2

@tanatan Always try to include edge cases as part of the question, not as an afterthought. People shouldn't have to provide you free answers _multiple times_ as you cobble together additional requirements. For example, does `2.1.1.5` sort before or after `2.1.1.b`? – Aaron Bertrand Jul 26 '22 at 11:33
That requirement completely invalidates *both* answers here, @tanatan ; that's not "ok". I've rolled that requirement back from your question because it's a breaking change. I suggest you have a go, yourself, at taking the solutions you have here to get the answer you want, and if you fail, then post a new question, with your new requirements, and show **your** attempts, and *explain* why `'a'` needs to be treated as a number, or why `'1'` not as a letter and what the "correct" order is. – Thom A Jul 26 '22 at 11:39

score 0 · Answer 2 · edited Jul 26 '22 at 11:24

0

This looked interesting so I have an answer too, a bit longer than Larnu's one, looking at the execution plan this does exactly the same thing. But theirs seems more succinct.

DROP TABLE IF EXISTS #DataTable;

CREATE TABLE #DataTable (
    Clause VARCHAR(20)
);

INSERT INTO #DataTable 
VALUES
('1.'),
('1.0'),
('1.1.1.'),
('1.1.2.'),
('1.10.'),
('1.2.'),
('2.'),
('2.1.1.'),
('3.')

SELECT Clause FROM 
(
    SELECT 
    Clause,
    CAST(REVERSE(PARSENAME(REVERSE(Clause), 1)) AS INT) AS Major,
    CAST(REVERSE(PARSENAME(REVERSE(Clause), 2)) AS INT) AS Minor,
    CAST(REVERSE(PARSENAME(REVERSE(Clause), 3)) AS INT) AS Patch
    FROM #DataTable
) AS T
ORDER BY T.Major, T.Minor, T.Patch

edited Jul 26 '22 at 11:24

Thom A

88,727
11
45
75

answered Jul 26 '22 at 11:09

Andrew

2,571
2
31
56

1

Thanks @Andrew but Clause column values could have many number of decimal values and have alpha characters too so this may not be the ideal one for my problem but still a good solution. Thanks. – tanatan Jul 26 '22 at 11:41
I think you might want to have a migration to split these into different columns e.g Major, Minor, Patch whatever names you want then you wont need to do this and you can just sort by Major, Minor, Patch. SQL would be happier as it wont have to do the parsing. – Andrew Jul 26 '22 at 12:19

Ordering the data by a column which has numbering/indexation type data with multiple decimals

2 Answers2