Solved

How to sort varchar field.

Posted on 2012-03-14
8
312 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
While in ##Table - Help 4 22
Sql query with where clause 2 45
Create SQL Server Job to perform backups of all the databases in a server instance 17 46
SQL- GROUP BY 4 21
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

680 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