Sorting Issue - 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 2008 Forums

Transact-SQL (2008)

Sorting Issue

Author

Topic

Sarvesh
Starting Member

1 Post

Posted - 2010-09-07 : 02:18:02

I have to sort the data.
Following conditions should be considered while sorting
1)If data contains only numbers then it should come first
2)If data contains numbers at the start of the string then it should be sorted such that numbers will come first.

Example
Consider following data
1234
4321
9872
12
abc
ert
12-k
12-a
78,t

if ascending sort than data should appear like follows
12
1234
4321
9872
12-a
12-k
12-t
abc
ert

if descending sort then
ert
abc
12-t
12-k
12-a
9872
4321
1234

12

how to do this
Any help will be appreciated

Sar

jmiller121
Starting Member

8 Posts

Posted - 2010-09-07 : 10:28:42

SELECT Col1, IsNumeric(Col1) as Col2
FROM YourTable
ORDER BY Col2 DESC, Col1

Not sure if the above syntax is entirely correct, but the theory is that you will make a second column in your view determine whether or not the first column has a numeric value. If so, it returns 1, otherwise 0. Since you want the numbers first, you will have to sort the second column Descending. For the rest of the values you will sort them Ascending to get the order you specified above.

nathans
Aged Yak Warrior

938 Posts

Posted - 2010-09-07 : 17:03:49

Same idea, but just do in the order by without actually materializing a column in a view.

declare @yak table (SortThis varchar(10))
insert into @yak
		  select '12'
union all select '1234'
union all select '4321'
union all select '9872'
union all select '12-a'
union all select '12-k'
union all select '12-t'
union all select 'abc'
union all select 'ert'

select * from @yak order by isnumeric(SortThis) desc, SortThis asc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-08 : 05:28:55

Also try

select * from @yak order by case when SortThis not like '%[^0-9]%' then 1 else 0 end desc, SortThis asc

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources