CODEWITHSUNDEEP
sqlsql-order-bycase
Michael
Michael

Reputation: 719

Select Case is not working with Order by

I was using a simple sql query and getting an ordered list, but when I changed some of the values in the column I'm sorting by, those rows were no longer being sorted correctly. 

select distinct u.Email, 
  case 
    when l.region_id is null then 'EU' 
    else l.region_id 
  end 
  as Location 
from TB_User u 
left join cat..location l on l.location=u.Location 
where u.Username in (....)
order by l.region_id

I have about 5 rows that returned null for their region_id so they would be at the top of the result set. When I added the case and replaced their value, they still remain at the top. Is there anyway to make these rows sort according to their given value?

Upvotes: 1

Views: 1597

Answers (3)

programmer43229
programmer43229

Reputation: 386

If you are using SQL, try within the SELECT statement, use:

ISNULL(l.region_id, 'EU') AS Location

and then 

ORDER BY 2

This will make your query:

SELECT DISTINCT u.Email, ISNULL(l.region_id, 'EU') AS Location 
FROM TB_User u 
LEFT JOIN cat..location l ON l.location=u.Location 
WHERE u.Username in (....)
ORDER BY 2

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460098

You can use CASE also in the ORDER BY. But in this case it seems that you instead want to order by the column which uses the CASE.

ORDER BY Location

If you instead want the null-regions at the bottom:

ORDER BY CASE WHEN l.region_id is null THEN 0 ELSE 1 END DESC,  
         Location  ASC

If your rdbms doesn't support this (like SQL-Server does) you have to repeat it:

ORDER BY CASE WHEN l.region_id IS NULL THEN 'EU' ELSE l.region_id END ASC

Upvotes: 3

Josh Alvo
Josh Alvo

Reputation: 96

You just order by the column value, which is null. If you want to order by the case statement, just copy it in the order by clause:

order by   
  case 
    when l.region_id is null then 'EU' 
    else l.region_id end

Upvotes: 0

Related Questions