• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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.
0
searchsanjaysharma
Asked:
searchsanjaysharma
  • 2
  • 2
  • 2
  • +2
1 Solution
 
jogosCommented:
order by
case when priority = 'high' then 1
when priority = 'medium' then 2
when priority = 'low' then 3
else end
0
 
SANDY_SKCommented:
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
 
micropc1Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
lcohanDatabase AnalystCommented:
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
 
searchsanjaysharmaAuthor Commented:
i want display as High,Medium,Low  and as Low Priority
0
 
SANDY_SKCommented:
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
 
jogosCommented:
<<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
 
searchsanjaysharmaAuthor Commented:
ok
0
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

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now