Solved

How to sort varchar field.

Posted on 2012-03-14
8
311 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 39

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 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

837 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