CODEWITHSUNDEEP
sql.netsql-servert-sqlsql-order-by
Codebadger
Codebadger

Reputation: 35

SQL order by specific values

I have the following data in SQL SERVER DB

enter image description here

on which an ordering is needed.

The ordering is to be done on specific value of priority

After ordering on priority, rows with the same priority will be ordered alphabetically

So the expected output is

enter image description here

I have done the following code

SELECT SERVICELONGNAME,SERVICEPRIORITYID FROM SERVICE

ORDER BY  

  CASE  
            WHEN SERVICEPRIORITYID > 0 THEN 0 ELSE 1   
            END
,SERVICELONGNAME ASC
enter code here

But in this case the rows with priority 0 is shown at the bottom but for SERVICEPRIORITYID 1,2,3,4 it is jumbled up.

Please can someone tell me what changes are required to match with the required output?

Upvotes: 0

Views: 83

Answers (2)

Fahmi
Fahmi

Reputation: 37473

You can try the below -

SELECT SERVICELONGNAME,SERVICEPRIORITYID 
FROM SERVICE
ORDER BY  CASE  
            WHEN SERVICEPRIORITYID=0 THEN 1 ELSE 0 END,SERVICELONGNAME ASC

Upvotes: 0

Preben Huybrechts
Preben Huybrechts

Reputation: 6111

Try this

SELECT SERVICELONGNAME
    ,SERVICEPRIORITYID
FROM SERVICE
ORDER BY CASE 
        WHEN SERVICEPRIORITYID > 0
            THEN SERVICEPRIORITYID
        ELSE 2147483647 -- Int max value
        END
    ,SERVICELONGNAME ASC enter code here

When SERVICEPRIORITYID is greater then 0 order by SERVICEPRIORITYID else order by int max value.

Upvotes: 1

Related Questions