sort in specific order - 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)

sort in specific order

Author

Topic

ajmufic
Starting Member

17 Posts

Posted - 2009-07-16 : 05:55:17

I wanna sort my resultset on a field which contains an int value, in ascending order.
BUT! I want all numbers starting with the number 1, to come in the end!!

So If I have the values:
34112
11440
25647
99999
13456

I would like them to be sorted like this:
25647
34112
99999
11440
13456

Any ideas of how to do this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-16 : 06:19:48

order by case when substring(cast(col as varchar(10)),1,1)=1 then 1 else 0 end,col

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2009-07-16 : 07:20:25

Hi Try with this

Declare @T Table
(
Id Int
)
Insert into @T
Select 34112 Union All
Select 11440 Union All
Select 25647 Union All
Select 99999 Union All
Select 13456

Declare @Temp Table
(
Id Int
)
Insert into @Temp
Select Id From @T
Where SUBSTRING(Cast(id as varchar(12)),1,1) <> 1
order by Id

Insert into @Temp
Select * From @T
Where SUBSTRING(Cast(id as varchar(12)),1,1) = 1

Select * From @Temp

ajmufic
Starting Member

17 Posts

Posted - 2009-07-16 : 07:52:54

quote:
Originally posted by madhivanan

order by case when substring(cast(col as varchar(10)),1,1)=1 then 1 else 0 end,col

Thanks madhivanan, works like a charm!!!

ajmufic
Starting Member

17 Posts

Posted - 2009-07-16 : 07:54:07

quote:
Originally posted by ranganath

Hi Try with this

[...]

Select * From @Temp

Thank you as well ranganath!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-16 : 08:33:49

quote:
Originally posted by ranganath

Hi Try with this

Declare @T Table
(
Id Int
)
Insert into @T
Select 34112 Union All
Select 11440 Union All
Select 25647 Union All
Select 99999 Union All
Select 13456

Declare @Temp Table
(
Id Int
)
Insert into @Temp
Select Id From @T
Where SUBSTRING(Cast(id as varchar(12)),1,1) <> 1
order by Id

Insert into @Temp
Select * From @T
Where SUBSTRING(Cast(id as varchar(12)),1,1) = 1

Select * From @Temp

There is no guarantee it will always work until order by is specified in the select statement

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-16 : 08:35:30

quote:
Originally posted by ajmufic

quote:
Originally posted by madhivanan

order by case when substring(cast(col as varchar(10)),1,1)=1 then 1 else 0 end,col
Thanks madhivanan, works like a charm!!!

You are welcome

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-19 : 19:55:53

quote:
Originally posted by ajmufic

I wanna sort my resultset on a field which contains an int value, in ascending order.
BUT! I want all numbers starting with the number 1, to come in the end!!

I've got to ask... what are the business requirements behind this requirement?

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources