CODEWITHSUNDEEP
sqlsql-servert-sql
Arthur Chaparyan
Arthur Chaparyan

Reputation: 2025

SQL Conditional Order By

I am doing a join on two tables. One is a user's table and the other a list of premium users. I need to have the premium members show up first in my query. However, just because they are in the premium user table doesn't mean they are still a premium member - there is an IsActive field that also needs to be checked.

So basically I need to return the results in the following order:

Right now I have it as the following:

SELECT Users.MemberId, PremiumUsers.IsActive FROM Users
LEFT JOIN PremiumUsers ON PremiumUsers.UserId = Users.Id
ORDER BY PremiumUsers.IsActive DESC

The problem with this is that it places non-active premium members above non-premium members.

(I'm using MS SQL Server 2005 for this)

Upvotes: 3

Views: 3348

Answers (2)

devio
devio

Reputation: 37205

try ORDER BY CASE

ORDER BY CASE
    WHEN PremiumUsers.IsActive = 1 THEN 1
    WHEN PremiumUsers.UserId IS NULL THEN 2
    ELSE 3
END

Upvotes: 12

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

ORDER BY COALESCE(PremiumUsers.IsActive, 0) DESC

That will group the NULLs with not-actives.

Upvotes: 7

Related Questions