We help IT Professionals succeed at work.

# How to sort varchar field.

on
I have a table a with field priority containing n records, and has the values as 'High','Low','Medium'.
in random manner the records are entered no indexing.
I can t add new field in table.
How do i sort the data so that High comes first, then Medium  and then Low.
Comment
Watch Question

## View Solution Only

Commented:
order by
case when priority = 'high' then 1
when priority = 'medium' then 2
when priority = 'low' then 3
else end

Commented:
Use the CASE statement like this....
``````select Description, CASE(Priority) WHEN 'High' then 3 WHEN 'Low' then 1 WHEN 'Medium' then 2 END as numericPriority from priorities order by numericPriority
``````
Senior Analyst - Technology
BRONZE EXPERT

Commented:
The best solution would be to normalize you data by creating a priority table with the following structure...

create table priority
( priority  varchar(10)
, orderIndex int
, CONSTRAINT priorityPK PRIMARY KEY (priority))

then join on that table and sort by the orderIndex.

But a quick and dirty solution would be to add a computed column...

ALTER TABLE tableName
CASE priority
WHEN 'High' THEN 1
WHEN 'Medium' THEN 2
WHEN 'Low' THEN 3
END

Then order by priorityIndex
Database Analyst
BRONZE EXPERT

Commented:
And make sure you have an index on the priority column plus at least the record key
column(s) in a INCLUDE of the same index.

Commented:
i want display as High,Medium,Low  and as Low Priority
Commented:
then

select Description, Priority, CASE(Priority) WHEN 'High' then 3 WHEN 'Low' then 1 WHEN 'Medium' then 2 END as numericPriority from priorities order by numericPriority

Commented:
<<i want display as High,Medium,Low>>
That is how the value is in your table ... that's why I only changed it in the ORDER BY

<<  and as Low Priority>>
this part I don't get

Commented:
ok