Order column with multiple dots in number

Question

Post reply

Order column with multiple dots in number

WesselN

SSC Enthusiast

Points: 190
More actions
June 14, 2012 at 2:39 am

#259771

Hi All,
I have the following table:
SELECT level
INTO #myTable
FROM(VALUES('1'),('1.1'),('1.2'),('2'),('3.1'),('10.1'),('10.2'),('11.1'),('11.2'),
('13.1'),('3.2'),('4'),('5'),('6'),('7'),('8'),('8.1'),('9'),('14'))a(level);
Default it will sort in the way it is created:
level
--------------
1
1.1
1.2
2
3.1
10.1
10.2
11.1
11.2
13.1
3.2
4
5
6
7
8
8.1
9
14
What I really want is the order shown below:
level
--------------
1
1.1
1.2
2
3.1
3.2
4
5
6
7
8
8.1
9
10.1
10.2
11.1
11.2
13.1
14
This cannot be realized with ordering by column level.
Is this possible in any way?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

You should do this in the presentation layer, rather than the database layer.

If for some reason can't and have to do it in the database layer, you could do it like this: -

SELECT level

FROM #myTable

ORDER BY CAST(SUBSTRING(level, 1, CASE WHEN CHARINDEX('.',level) > 0 THEN CHARINDEX('.',level) - 1 ELSE LEN(level) END) AS INT),

CAST(SUBSTRING(level, CASE WHEN CHARINDEX('.',level) > 0 THEN CHARINDEX('.',level) + 1 ELSE NULL END, LEN(level)) AS INT);

Results in: -

level

-----

1

1.1

1.2

2

3.1

3.2

4

5

6

7

8

8.1

9

10.1

10.2

11.1

11.2

13.1

14

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

Assuming your sample data is representative of the real data (i.e. level will always convert to numeric, you don't have lower levels e.g. 1.2.5), won't this do the trick?

select level from #mytable

order by convert(numeric(10,1),level)

WesselN SSC Enthusiast Points: 190 More actions · Answer 3

You are correct, Ian.

Unfortanetely there are lower levels (up to 99).

So it can be 1.1.1, 1.1.2, 1.1.3 etc.

I'm sorry Cadavre, the subject mentioned multiple dots, but as always I forget something in a post.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 4

Using Jeff Modens splitter here

http://www.sqlservercentral.com/articles/Tally+Table/72993/

SELECT level

FROM #myTable

ORDER BY (SELECT CAST(Item+1000 AS VARCHAR(10))+'/' AS "text()"

FROM dbo.DelimitedSplit8K(level,'.')

ORDER BY ItemNumber

FOR XML PATH(''));

____________________________________________________

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

WesselN SSC Enthusiast Points: 190 More actions · Answer 5

WesselN

SSC Enthusiast

Points: 190

June 14, 2012 at 4:19 am

#1500879

Thanks, worked perfectly.

Sony Francis @EY SSCommitted Points: 1539 More actions · Answer 6

Try this

SELECT level

INTO #myTable

FROM(VALUES('1'),('1.1.3'),('1.2.4.4'),('2'),('3.1'),('10.1'),('10.2'),('11.1'),('11.2'),

('13.1'),('3.2'),('4'),('5'),('6'),('7'),('8'),('8.1'),('9'),('14'))a(level);

;WITH cte

AS

(

SELECT [level], ROW_NUMBER() OVER(

PARTITION BY

SUBSTRING ([level], CASE WHEN CHARINDEX('.',[level],0)=0 THEN 1 ELSE 0 END, CASE WHEN CHARINDEX('.',[level],0)=0 THEN LEN([level]) ELSE CHARINDEX('.',[level],0) END )

ORDER by REPLACE([level],'.','')

) AS Id

FROM #myTable

)

SELECT [level] FROM cte

ORDER BY CONVERT(INT,SUBSTRING ([level], CASE WHEN charindex('.',[level],0)=0 THEN 1 ELSE 0 END, CASE WHEN charindex('.',[level],0)=0 THEN LEN([level]) else charindex('.',[level],0) end )),

Id

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

Sony Francis @EY (6/14/2012)
Try this
SELECT level
INTO #myTable
FROM(VALUES('1'),('1.1.3'),('1.2.4.4'),('2'),('3.1'),('10.1'),('10.2'),('11.1'),('11.2'),
('13.1'),('3.2'),('4'),('5'),('6'),('7'),('8'),('8.1'),('9'),('14'))a(level);
;WITH cte
AS
(
SELECT [level], ROW_NUMBER() OVER(
PARTITION BY
SUBSTRING ([level], CASE WHEN CHARINDEX('.',[level],0)=0 THEN 1 ELSE 0 END, CASE WHEN CHARINDEX('.',[level],0)=0 THEN LEN([level]) ELSE CHARINDEX('.',[level],0) END )
ORDER by REPLACE([level],'.','')
) AS Id
FROM #myTable
)
SELECT [level] FROM cte
ORDER BY CONVERT(INT,SUBSTRING ([level], CASE WHEN charindex('.',[level],0)=0 THEN 1 ELSE 0 END, CASE WHEN charindex('.',[level],0)=0 THEN LEN([level]) else charindex('.',[level],0) end )),
Id

I don't think this works properly with this data

SELECT level

INTO #myTable

FROM(VALUES('1'),('1.1.21.9'),('1.12.1.0')

)a(level)

____________________________________________________

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