Order column with multiple dots in number
-
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?
-
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 levelFROM #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/
-
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 #mytableorder by convert(numeric(10,1),level)
-
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.
-
Using Jeff Modens splitter here
http://qa.sqlservercentral.com/articles/Tally+Table/72993/
SELECT levelFROM #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
-
Thanks, worked perfectly.
-
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
-
Sony Francis @EY (6/14/2012)
Try thisSELECT 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 levelINTO #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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply