order by question 1

[mplbm]

If I have a field with different string values and I want to return data ordered depending upon the value of that string, how would I do that?

E.g. I have 3 possible string values for the field called 'ME', 'NY' and 'SC' and I want to order the results first by 'NY', then by 'SC' and then 'ME'.

Is this possible?

 

[SQLDenis]

yep see example

create table #test (state char(2))
insert into #test
select 'ME' union all
select 'ME' union all
select 'ME' union all
select 'SC' union all
select 'NY' union all
select 'SC' union all
select 'NY' union all
select 'SC' 

select * from #test
order by case state when 'NY' then '1'
 when 'SC' then '2'
else 3 end

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Slightly shorter, but not necessarily faster:

select * from #test
order by charindex(state, 'NY-SC-ME')

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

Cool code vongrunt.. didn't know that was possible...

Jim

 

[SQLDenis]

or even

SELECT   *
FROM     #TEST
ORDER BY CHARINDEX(STATE,'SC-ME')

since the charindex will be 0 for NY

SELECT   *,CHARINDEX(STATE,'SC-ME')
FROM     #TEST
ORDER BY CHARINDEX(STATE,'SC-ME')

so in order to translate this

SELECT   *
FROM     #TEST
ORDER BY CASE STATE 
           WHEN 'NY' THEN 1
           WHEN 'SC' THEN 2
           ELSE 3
         END

you need to use

SELECT   *
FROM     #TEST
ORDER BY CHARINDEX(STATE,'ME-SC-NY') DESC

If you have other values and you want them to be sorted last


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/