sorting data to length - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sorting data to length

Author

Topic

exorbitant
Starting Member

17 Posts

Posted - 2008-12-22 : 10:41:36

Hi All,
I am trying to sort the data according to its length. query returns data in this form:
1
10
100
101
101110
101112
11
110
1101
11011
11012
12
120
121
1210

and so on but I want data in the following form
1
10
11
12
100
101
110
and so on.
any help...?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-22 : 10:44:30

quote:
Originally posted by exorbitant

Hi All,
I am trying to sort the data according to its length. query returns data in this form:
1
10
100
101
101110
101112
11
110
1101
11011
11012
12
120
121
1210

and so on but I want data in the following form
1
10
11
12
100
101
110
and so on.
any help...?

Your column must be varchar,char. Order it by convert(int,columnname)

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-22 : 10:46:42

either:

Order by Len(column1)
or
Order by cast(column1 as int)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-22 : 10:58:48

quote:
Originally posted by hanbingl

either:

Order by Len(column1)
or
Order by cast(column1 as int)

Order by Len(column1) might not work.
See this,

create table xyz (a int)
go
insert into xyz
select 5 union
select 4 union
select 3
go
insert into xyz
select 2 union
select 1 union
select 10

go
select * from xyz order by len(a)

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-22 : 11:21:36

yup, all depends on what he's trying to do. If he want to sort as numeric or just length.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-22 : 22:56:15

select * from table order by columnname

doesn't require len(colname)

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-22 : 23:03:42

SELECT * FROM xyz ORDER BY LEN(column),column

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-22 : 23:05:01

select * from xyz group by a

I Struggle For Excellence

exorbitant
Starting Member

17 Posts

Posted - 2008-12-23 : 05:06:46

hi

thanks for kind replies.

SELECT * FROM xyz ORDER BY LEN(column),column works in my case.

other r also working well but not like i want and there comes 'integer overflow' exception in some of them as I've even 15 digit numbers as well in the field.

Thanks again for all king responses
Regards,

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-23 : 05:40:06

Welcome

Jai Krishna

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-12-23 : 06:26:29

I would use the order by Len(colname) asc or desc depending on what you wanted

exorbitant
Starting Member

17 Posts

Posted - 2008-12-23 : 07:33:55

quote:
Originally posted by NeilG

I would use the order by Len(colname) asc or desc depending on what you wanted

I've checked for this mean asc and desc but this option doesnt work in my case really

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-24 : 04:35:44

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources