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: Active Premium Users Regular and Inactive Premium Users
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)
ORDER BY COALESCE(PremiumUsers.IsActive, 0) DESC
That will group the NULLs with not-actives.
try ORDER BY CASE
ORDER BY CASE WHEN PremiumUsers.IsActive = 1 THEN 1 WHEN PremiumUsers.UserId IS NULL THEN 2 ELSE 3 END