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)