CODEWITHSUNDEEP
sqlsql-server
user3807187
user3807187

Reputation: 185

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

Upvotes: 2

Views: 109

Answers (7)

remya krishnan
remya krishnan

Reputation: 1

declare @flttaxrate float
declare @fltArea float
declare @fltbasetax float

set @fltArea=139.3
set @flttaxrate=50

set @fltbasetax=@fltArea*@flttaxrate
select @fltbasetax
select ceiling(@fltbasetax)

please check the output select @fltbasetax, select ceiling(@fltbasetax)

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 1

Shidai
Shidai

Reputation: 227

This one works for me in HANA

SELECT AttendanceCode, AttendanceDescription
FROM journalattendancestatus
ORDER BY (CASE WHEN AttendanceDescription= 'Present' THEN 1 ELSE 0 END) DESC

Upvotes: 0

Prafulla
Prafulla

Reputation: 585

Your second query is working. It has syntax error. I have removed ',' near END statement and double quotes at the end of query.

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

In first query you are searching for a letter 'P' which is not present in your column. You can use like here. 

ORDER BY (CASE when AttendanceDescription like 'Pre%' THEN 1 ELSE NULL END) AttendanceDescription

Upvotes: 0

Stephen Zeng
Stephen Zeng

Reputation: 2818

Not the best option but just an idea:

SELECT AttendanceCode, AttendanceDescription, 1 AS OrderIndex 
FROM journalattendancestatus
WHERE AttendanceDescription = 'P' 
UNION
SELECT AttendanceCode, AttendanceDescription, 2 AS OrderIndex 
FROM journalattendancestatus
WHERE AttendanceDescription != 'P' 
ORDER BY OrderIndex, AttendanceCode, AttendanceDescription

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28900

for this query nulls will come first..

ORDER BY (CASE AttendanceDescription WHEN 'P' THEN 1 ELSE NULL END) AttendanceDescription

Change it to 

WHEN 'P' THEN 1 ELSE 2 END

second query is almost close..

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

But when 1 has ties it again falls back to AttendanceDescription to break ties,so in this case it can be any thing..so remove that

Upvotes: 0

Lulceltech
Lulceltech

Reputation: 1702

Order by is simply to sort your data by a column.

Example:

SELECT * FROM user WHERE AGE=18 ORDER BY NAME DESC

That will take your SQL and order it in reverse alphabetical order.

What exactly are you trying to do? I might be able to give you a solution to what you're trying to accomplish.

Upvotes: 0

Related Questions