sorting a column

Question

sorting a column

ishaan99

SSCertifiable

Points: 6261
More actions
July 26, 2005 at 11:14 am

#90285

I have a column which has values as
1
2
2.1
3
4
2.1.1
2.1.1.1
2.1.2
2.1.3
2.2
2.1.4
2.3
2.99
2.99.1
2.1.5
2.99.1.1
2.1.6
2.100
2.100.1
2.101
2.102
2.1.7

and i need to get this sorted as

1
2
2.1
2.1.1
2.1.1.1
2.1.2
2.1.3
2.1.4
2.1.5
2.1.6
2.1.7
2.2
2.99
2.99.1
2.99.1.1
2.100
2.100.1
2.101
2.102

Is this comething doable thru sql. any help will be greatly appreciated. Thanks

Viewing 15 posts - 1 through 15 (of 29 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Have you tried ORDER BY ColumnX? If YES and that failed you may need to apply an ORDER BY type INTEGER column and then use that for ordering purposes only.....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

ishaan99 SSCertifiable Points: 6261 More actions · Answer 2

But how can you have this as an int column when you ahve data as 2.1.1.2 and so on...any further hint please??

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I think this may work for you

CREATE TABLE #Numbers( Number varchar(15))

INSERT INTO #Numbers VALUES( '1')

INSERT INTO #Numbers VALUES( '2')

INSERT INTO #Numbers VALUES( '2.1')

INSERT INTO #Numbers VALUES( '3')

INSERT INTO #Numbers VALUES( '4')

INSERT INTO #Numbers VALUES( '2.1.1')

INSERT INTO #Numbers VALUES( '2.1.1.1')

INSERT INTO #Numbers VALUES( '2.1.2')

INSERT INTO #Numbers VALUES( '2.1.3')

INSERT INTO #Numbers VALUES( '2.2')

INSERT INTO #Numbers VALUES( '2.1.4')

INSERT INTO #Numbers VALUES( '2.3')

INSERT INTO #Numbers VALUES( '2.99')

INSERT INTO #Numbers VALUES( '2.99.1')

INSERT INTO #Numbers VALUES( '2.1.5')

INSERT INTO #Numbers VALUES( '2.99.1.1')

INSERT INTO #Numbers VALUES( '2.1.6')

INSERT INTO #Numbers VALUES( '2.100')

INSERT INTO #Numbers VALUES( '2.100.1')

INSERT INTO #Numbers VALUES( '2.101')

INSERT INTO #Numbers VALUES( '2.102')

INSERT INTO #Numbers VALUES( '2.1.7')

SELECT * FROM #Numbers

ORDER BY CONVERT( decimal(10,8), SUBSTRING( REPLACE( Number, CHAR(46), ''), 1, 1) + '.' + SUBSTRING( REPLACE( Number, CHAR(46), ''), 2, 14) )

DROP TABLE #Numbers

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 4

if you have four dots('.') (IP addresses)

then

Select *

from Table

order by

cast(parsename(ColX,4) as int)

,cast(parsename(ColX,3) as int)

,cast(parsename(ColX,2) as int)

,cast(parsename(ColX,1) as int)

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

darn but you stole my idea noel - have been saving parsename for just such an occasion as this...btw - sometime in the past (many moons ago) - i did test parsename with 2 and 3 dots & it worked equally fine with < 4.............

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 6

... worked equally fine with <= 4.............

* Noel

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Noel is correct.

Mine orders this like an Index to a document and that is not what you want. (I did not notice that in your desired order).

I wasn't born stupid - I had to study.

sushila SSC-Dedicated Points: 35293 More actions · Answer 8

have to have the last word don't you ?! okay - worked equally fine with <= 4!

**ASCII stupid question, get a stupid ANSI !!!**

ishaan99 SSCertifiable Points: 6261 More actions · Answer 9

thanks but this givesa slightly different result than expected.i need same as in my initial post. i guess i need to tweak the query to do that.

ishaan99 SSCertifiable Points: 6261 More actions · Answer 10

Noel, this result

1

2

3

4

2.1

2.2

2.3

2.99

2.100

2.101

2.102

2.1.1

2.1.2

2.1.3

2.1.4

2.1.5

2.1.6

2.1.7

2.99.1

2.100.1

2.1.1.1

2.99.1.1

doesnt match up with my initial listing is there a way to get the result as posted earlier??thanks

noeld SSC Guru Points: 96590 More actions · Answer 11

Sorry try this!

Select number

from MyTable

order by

ISnull(cast(parsename(Number + replicate('.0',3 - len(Number) + len(replace(number,'.',''))),4) as int),0)

,ISnull(cast(parsename(Number + replicate('.0',3 - len(Number) + len(replace(number,'.',''))),3) as int),0)

,ISnull(cast(parsename(Number + replicate('.0',3 - len(Number) + len(replace(number,'.',''))),2) as int),0)

,ISnull(cast(parsename(Number + replicate('.0',3 - len(Number) + len(replace(number,'.',''))),1) as int),0)

* Noel

ishaan99 SSCertifiable Points: 6261 More actions · Answer 12

ishaan99

SSCertifiable

Points: 6261

July 26, 2005 at 2:09 pm

#577302

Thanks you are awesome...

noeld SSC Guru Points: 96590 More actions · Answer 13

noeld

SSC Guru

Points: 96590

July 26, 2005 at 2:10 pm

#577305

Happy to Help

* Noel

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 14

Stealing ideas of everyone else, I suggest the following:

SELECT * from #Numbers

order by

convert(int, reverse(parsename(reverse(Number),1))),

convert(int, reverse(parsename(reverse(Number),2))),

convert(int, reverse(parsename(reverse(Number),3))),

convert(int, reverse(parsename(reverse(Number),4)))