Interactive sort on a text field having decimal numbers

Question

Interactive sort on a text field having decimal numbers

rockstar283

SSCrazy

Points: 2704
More actions
June 18, 2015 at 7:58 pm

#301774

I have the following text field in SSRS report:
Version
2.0.0.0
1.0.0.0
1.2.0.0
2.1.8.8
2.2.32.7
1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly?
Once sorted ascending, report should show
Version
1.0.0.0
1.2.0.0
1.4.11.0
2.0.0.0
2.1.8.8
2.2.32.7
Thank you in advance

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

rockstar283 (6/18/2015)
I have the following text field in SSRS report:
Version
2.0.0.0
1.0.0.0
1.2.0.0
2.1.8.8
2.2.32.7
1.4.11.0
I want to sort this field interactively.I have already sorted other fields, but as this field is text but has decimal data, its not sorting properly. How do I do this correctly?
Once sorted ascending, report should show
Version
1.0.0.0
1.2.0.0
1.4.11.0
2.0.0.0
2.1.8.8
2.2.32.7
Thank you in advance

First, see the first Blue link in my signature line below for how to post problems like this to get the best help.

The following will work provided that you always have 4 items separated by periods on each row. If that's not right, then post the readily consumable data like it says in the article. Thanks.

WITH

cteTestData (YourColumn) AS

(

SELECT '2.0.0.0' UNION ALL

SELECT '1.0.0.0' UNION ALL

SELECT '1.2.0.0' UNION ALL

SELECT '2.2.32.7' UNION ALL

SELECT '2.2.8.8' UNION ALL --Changed this to show it works

SELECT '1.4.11.0'

)

SELECT *

FROM cteTestData

ORDER BY PARSENAME(YourColumn,4)+0

,PARSENAME(YourColumn,3)+0

,PARSENAME(YourColumn,2)+0

,PARSENAME(YourColumn,1)+0

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rockstar283 SSCrazy Points: 2704 More actions · Answer 2

Thank you for your input mate.

I have found the solution for this. I break down the strings and add trailing zeros and make their length is 8(in my case). e.g. '1.2.11.0' becomes '01021100' and so on..then convert this to Integer and then in the interactive sort, use this integer field to sort.