Geek asks and answers

SQL Conditional Order

I am doing a join on two tables. One of them is a user table, and the other is a list of premium users. I need the prize participants to appear in my request. However, just because they are in the premium user table does not mean that they are still a premium member - there is an IsActive field that also needs to be checked.

Therefore, basically I need to return the results in the following order: Active Premium Users Normal and Inactive Premium Users

Now I have it like: SELECT Users.MemberId, PremiumUsers.IsActive FROM Users LEFT JOIN PremiumUsers ON PremiumUsers.UserId = Users .Id ORDER BY PremiumUsers.IsActive DESC

The problem is that it places inactive premium members above non-premium members.

(I use MS SQL Server 2005 for this)

+3
source share
2 answers
ORDER BY COALESCE(PremiumUsers.IsActive, 0) DESC

This will group NULL with inactive.

+7
source

try ORDER BY CASE

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

More articles:


All Articles