Solved

How to sort varchar field.

Posted on 2012-03-14
8
310 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now