?
Solved

How to sort varchar field.

Posted on 2012-03-14
8
Medium Priority
?
323 Views
Last Modified: 2012-03-15
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.
0
Comment
Question by:searchsanjaysharma
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37721786
order by
case when priority = 'high' then 1
when priority = 'medium' then 2
when priority = 'low' then 3
else end
0
 
LVL 11

Expert Comment

by:SANDY_SK
ID: 37721808
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

0
 
LVL 7

Expert Comment

by:micropc1
ID: 37721820
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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 40

Expert Comment

by:lcohan
ID: 37721825
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.
0
 

Author Comment

by:searchsanjaysharma
ID: 37721834
i want display as High,Medium,Low  and as Low Priority
0
 
LVL 11

Accepted Solution

by:
SANDY_SK earned 1500 total points
ID: 37721840
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
0
 
LVL 25

Expert Comment

by:jogos
ID: 37721960
<<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
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 37726167
ok
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question