CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jamesla
Jamesla

Reputation: 1418

sql order by hardcoded values

I have the following query:

select 'junior' as type, value
from mytable
union
select 'intermediate' as type, value
from mytable
union
select 'senior' as type, value
from mytable

Which returns the following data:

type          value

Intermediate     10    
Junior            5
Senior            1

I just need to reorder it so it looks like this

Junior          5
Intermediate   10        
Senior          1

I can't figure out which order by clause to use to achieve ordering by custom specific values, how would I achieve this?

Upvotes: 2

Views: 3764

Answers (4)

The Hill Boy
The Hill Boy

Reputation: 162

Try this one.

select TYPE, value from ( select type,value , row_number() over ( order by type ) as rowno from #mytable union select type, value, row_number() over ( order by type ) as rowno from #mytable union select type,value, row_number() over ( order by type ) as rowno from #mytable )a order by 2 desc

Upvotes: -1

bdeem
bdeem

Reputation: 909

You can either sort by adding a sort key column or add a simple case statement based on the values.

-- Sort with Case statement
with sourceData as
(
  select 'junior' type, 5 value
  union all
  select 'intermediate' type, 10 value
  union all
  select 'senior' type, 1 value
)
select * 
from sourceData
order by 
  case type
    when 'junior' then 0
    when 'intermediate' then 1
    when 'senior' then 2
    else null
  end

SQL Fiddle for testing.

Upvotes: 3

tkestowicz
tkestowicz

Reputation: 332

I don't know if understood you well but I would extract one more column e.g. "sequence" with integer values. It will provide to the ability of applying custom order.

As an example:

select results.type, results.value, results.sequence 
from (    
    select 'junior' as type, value, 0 as sequence
    from mytable
    union
    select 'intermediate' as type, value, 1 as sequence
    from mytable
    union
    select 'senior' as type, value, 2 as sequence
    from mytable
) as results order by results.sequence

Upvotes: 2

cha
cha

Reputation: 10411

You need to add a third column, called e.g. sortorder. Then, assign the proper integer value to it,

select 'junior' as type, value, 1 as sortorder
from mytable
union
select 'intermediate' as type, value, 2 as sortorder
from mytable
union
select 'senior' as type, value, 3 as sortorder
from mytable
order by 3

Upvotes: 2

Related Questions