user3807187 user3807187 - 3 months ago 13
SQL Question

SQL ORDER BY Doubts

|--------------------- |------------------ |
| AttendanceCode | AttendanceDescription |
|--------------------- |------------------ |
| MC | Medical Leave |
|--------------------- |------------------ |
| NAPFA | NAPFA |
|--------------------- |------------------ |
| Present | Present |
|--------------------- |------------------ |


I'm using SQL Server 2014. I have a table with two columns, AttendanceCode and AttendanceDescription and I'm using a dropdownlist to display the AttendanceDescription. I would like the dropdownlist order to display "Present" first. I tried using this query:

SELECT AttendanceCode, AttendanceDescription
FROM journalattendancestatus
ORDER BY (CASE AttendanceDescription WHEN 'P' THEN 1 ELSE NULL END) AttendanceDescription


And this

SELECT AttendanceCode, AttendanceDescription
FROM journalattendancestatus
ORDER BY CASE WHEN AttendanceDescription = 'Present' THEN 1 ELSE 2 END,
AttendanceDescription";


But none of them work. I've also tried to change it to AttendanceCode where 'P' represent 'Present'.

Answer

Use AttendanceDescription column only once in ORDER BY clause

SELECT AttendanceCode, AttendanceDescription
FROM journalattendancestatus
ORDER BY CASE WHEN AttendanceCode = 'Present' THEN 1  
              WHEN AttendanceCode = 'MC'      THEN 2  
              WHEN AttendanceCode = 'NAPFA'   THEN 3 
          END     
Comments