order by clause in sql server

Question

1.00/5 (1 vote)

See more:

hi,

in my table i am having a column STATUS which is having records like this

ID NAME STATUS
101 ABCD 1
102 XYZ 2
103 XXX 3
104 DEF 4

i want to get output in the following format

ID NAME STATUS
102 XYZ 2
101 ABCD 1
104 DEF 4
103 XXX 3

Posted 17-Jul-13 18:11pm

ali from hyd

Add a Solution

Comments

ArunRajendra 18-Jul-13 0:31am

What is the criteria for getting this order?

ali from hyd 18-Jul-13 0:36am

for my ticketing functionality i am having status as 1-Open ,2-Reply , 3-Closed ,4-Reopen.

in grid i want to display the records it in the following order 2,1,4,3

Sergey Alexandrovich Kryukov 18-Jul-13 0:40am

Again, what are the ordering criteria? Did you understand the question? Not an example, the criteria?
—SA

ali from hyd 18-Jul-13 0:54am

i have to display the Ticket which is in Replied Status first
Open Status second
Reopen Status Third
Closed Status Fourth

2 solutions

Solution 2

Assuming you have a status table that contains all the provided status. You can add a column in that status table named ORDERING_INFO.

SQL

ALTER TABLE STATUS ADD ORDERING_INFO INT NULL

UPDATE STATUS SET ORDERING_INFO = 2 WHERE STATUS_ID = 1;
UPDATE STATUS SET ORDERING_INFO = 1 WHERE STATUS_ID = 2;
UPDATE STATUS SET ORDERING_INFO = 4 WHERE STATUS_ID = 3;
UPDATE STATUS SET ORDERING_INFO = 3 WHERE STATUS_ID = 4;

You can then modify your select query like this

SQL

SELECT ID,NAME
FROM   TABLE 
       INNER JOIN STATUS ON TABLE.STATUS = STATUS.STATUS_ID
ORDER BY STATUS.ORDERING_INFO

Posted 17-Jul-13 19:23pm

_Asif_

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ArunRajendra · Accepted Answer · 2013-07-17T18:47:00

Solution 1

Try this code. Replace @t with your table name.

SQL

select id,NAME,
case
when status=2 then 1
when status=1 then 2
when status=4 then 3
when status=3 then 4
end as newstatus
from @t
order by newstatus

Posted 17-Jul-13 18:47pm

ArunRajendra

Comments

ali from hyd 18-Jul-13 1:50am

nice thank u

ArunRajendra 18-Jul-13 1:58am

Can you please mark it as answered.

ArunRajendra 18-Jul-13 1:57am

welcome