asp.net sql order by certain value

Question

hi i have an sql statement that returns rows from a table in descending order, the column i am ordering by is requestStatus. the requestStatus can be 3 values pending, approved and rejected . i would like to alter the sql if possible to order by pending then approved and then rejected. my sql statement is below. i am using asp.net razor view engine. can anyone point me in the right direction ?

   var dbCommand2 = "SELECT * FROM LeaveRequests WHERE email = @0 ORDER BY WHERE requestStatus DESC";
   var rows1 = db.Query(dbCommand2, theUserName);

What is that WHERE after ORDER BY?

Steve
– Steve

2014-04-30 22:51:47 +00:00
Commented Apr 30, 2014 at 22:51 — Steve
– Steve, Commented Apr 30, 2014 at 22:51

Dave Jemison · Accepted Answer · 2014-04-30 22:54:34Z

3

Use CASE in your SELECT statement:

SELECT *, CASE requestStatus
   WHEN 'pending' THEN 0
   WHEN 'approved' THEN 1
   WHEN 'rejected' THEN 2
   END  AS requestStatusOrder
FROM LeaveRequests
WHERE email = @0
ORDER BY requestStatusOrder

answered Apr 30, 2014 at 22:54

Dave Jemison

7043 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

You can just put the case directly in the order by and not have to add an additional column to the select.

derek Over a year ago

Hi dave, great thanks for help , that works perfectly . i'll will also try your way gordon , thanks everyone for their help

Collectives™ on Stack Overflow

asp.net sql order by certain value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related