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 2000 Forums
 Transact-SQL (2000)
 order by for alpha numeric as alpha first then num

Author  Topic 

joseln
Starting Member

11 Posts

Posted - 2007-11-23 : 03:03:59
Hi,

I have a field value in my table as

1
a
b
2
c
3

but i want to display in my select query as

a
b
c
1
2
3

pls suggest how to write the "order by" in my select query.

thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-23 : 03:15:29
Something like this:


-- Prepare Sample data
declare @t table
(
a varchar(10)
)

insert @t
select '1' union all
select 'a' union all
select 'b' union all
select '2' union all
select 'c' union all
select '3'

-- Final query
select * from @t
order by case when a like '[a-z]' then 1 else 2 end, a


Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

joseln
Starting Member

11 Posts

Posted - 2007-11-23 : 03:34:03
Hi, Thanks

by the way, what 1 and 2 represent in the case statement of the order by clause.

case when a like [a-z] then 1 else 2 end

thanks again
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-23 : 03:49:46
1 and 2 represents..1 and 2 only!!

You can consider them as rank since you want Alphabets higher in the order, I ranked them as rank 1 and anything other than that as rank 2.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-23 : 04:28:35
What if you have value like 'ab5'?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-23 : 04:36:01
Well in that case OP has to tell what should be the ranking of 'ab5'? Whether along with alphabets, between alphabets and numbers or after numbers?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-23 : 06:24:26
select * from @t
order by case when a not like '%[^0-9]%' then 2 else 1 end, len(a), a



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -