We help IT Professionals succeed at work.

How to sort varchar field.

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

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

Open in new window

Chris AshcraftSenior 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
ADD priorityIndex AS
   CASE priority
   WHEN 'High' THEN 1
   WHEN 'Medium' THEN 2
   WHEN 'Low' THEN 3
   END

Then order by priorityIndex
lcohanDatabase 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.

Author

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

Author

Commented:
ok

Explore More ContentExplore courses, solutions, and other research materials related to this topic.