Ordering rows that use the format 1.2.3 in a SQL query

Richard Fennell Oct 30, 2014 · 3 min read · Uncategorised ·

Share on:

Whilst working on a SSRS based report I had hit a sort order problem. Entries in a main report tablix needed to be sorted by their OrderNumber but this was in the form 1.2.3; so a neither a numeric or alpha sort gave the correct order i.e. a numeric sort fails as 1.2.3 is not a number and an alpha sort worked but give the incorrect order 1.3, 1.3.1, 1.3.10, 1.3.11, 1.3.12, 1.3.2.

When I checked the underlying SQL it turned out the OrderNumber was being generated, it was not a table column. The raw data was in a single table that contained all the leaf nodes in the hierarchy, the returned data was built by a SPROC using a recursive call.

The solution was to also calculate a SortOrder as well as the OrderNumber. I did this using a Power function on each block of the OrderNumber and added the results together. In the code shown below we can have any number of entries in the first block and up to 999 entries in the second or third block. You could have more by altering the Power function parameters

1declare @EntryID as nvarchar(50) = ‘ABC1234';

WITH SummaryList AS
    (
        SELECT
        NI.ItemID,
        NI.CreationDate,
        'P' as ParentOrChild,
        cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) as nvarchar(100)) as OrderNumber,
        cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) * Power(10,6) as int) as SortOrder
        FROM dbo.NotebookItem AS NI
        WHERE NI.ParentID IS NULL AND NI.EntryID = @EntryID

UNION ALL

        SELECT
        NI.ItemID,
        NI.CreationDate,
        'L' as ParentOrChild,
        cast(SL.OrderNumber + '.' + cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) as nvarchar(100)) as nvarchar(100)) as OrderNumber,
        SL.SortOrder + (cast(ROW_NUMBER() OVER(ORDER BY NI.CreationDate) as int) * power(10 ,6 - (3* LEN(REPLACE(sl.OrderNumber, '.', ''))))) as SortOrder
        FROM dbo.NotebookItem AS NI
        INNER JOIN SummaryList as SL
            ON NI.ParentID = SL.ItemID
    )

    SELECT
        SL.ItemID,
        SL.ParentOrChild,
        SL.CreationDate,
        SL.OrderNumber,
        SL.SortOrder
    FROM SummaryList AS SL
    ORDER BY SL.SortOrder

  1
  2This query returns the following with all the data correctly ordered
  3
  4ItemID
  5
  6ParentOrChild
  7
  8CreationDate
  9
 10OrderNumber
 11
 12SortOrder
 13
 1422F72F9E-E34C-45AB-A4D9-C7D9B742CD2C
 15
 16P
 17
 1829 October 2014
 19
 201
 21
 221000000
 23
 24E0B74D61-4B69-46B0-B0A9-F08BE2886675
 25
 26L
 27
 2829 October 2014
 29
 301.1
 31
 321001000
 33
 34CB90233C-4940-4312-81D1-A26CB540DF2A
 35
 36L
 37
 3829 October 2014
 39
 401.2
 41
 421002000
 43
 4435CCC2A1-E00F-43C6-9CB3-732342EE18DA
 45
 46L
 47
 4829 October 2014
 49
 501.3
 51
 521003000
 53
 547A920ABE-A2E2-4CF1-B36E-DE177A7B8681
 55
 56L
 57
 5829 October 2014
 59
 601.3.1
 61
 621003001
 63
 64C5E863A1-5A92-4F64-81C6-6946146F2ABA
 65
 66L
 67
 6829 October 2014
 69
 701.3.2
 71
 721003002
 73
 7423D89CFF-C9A3-405E-A7EE-7CAACCA58CC2
 75
 76L
 77
 7829 October 2014
 79
 801.3.3
 81
 821003003
 83
 84CE4F9F6B-3A58-4F78-9C1F-4780883F6995
 85
 86L
 87
 8829 October 2014
 89
 901.3.4
 91
 921003004
 93
 948B2A137F-C311-419A-8812-76E87D8CFA40
 95
 96L
 97
 9829 October 2014
 99
1001.3.5
101
1021003005
103
104F8487463-302E-4225-8A06-7C8CDCC23B45
105
106L
107
10829 October 2014
109
1101.3.6
111
1121003006
113
114D365A402-D3CC-4242-B1B9-356FB41AABC1
115
116L
117
11829 October 2014
119
1201.3.7
121
1221003007
123
124DFD4D688-080C-4FF0-B1D0-EBE63B6F99FD
125
126L
127
12829 October 2014
129
1301.3.8
131
1321003008
133
134272A46C6-E326-47E8-AEE4-952AF746A866
135
136L
137
13829 October 2014
139
1401.3.9
141
1421003009
143
144F073AFFA-F9A1-46ED-AC4B-E92A7160EB21
145
146L
147
14829 October 2014
149
1501.3.10
151
1521003010
153
154140744E7-4950-43F8-BA0C-0E541550F14B
155
156L
157
15829 October 2014
159
1601.3.11
161
1621003011
163
16493AA3C05-E95A-4201-AE03-190DDBF31B47
165
166L
167
16829 October 2014
169
1701.3.12
171
1721003012
173
1745CED791D-4695-440F-ABC4-9127F1EE2A55
175
176L
177
17829 October 2014
179
1801.4
181
1821004000
183
184FBC38F00-E2E8-4724-A716-AE419907A681
185
186L
187
18829 October 2014
189
1901.5
191
1921005000
193
1942862FED9-8916-4139-9577-C858F75C768A
195
196P
197
19829 October 2014
199
2002
201
2022000000
203
2048265A2BE-D2DD-4825-AE0A-7930671D4641
205
206P
207
20829 October 2014
209
2103
211
2123000000