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)

Answers


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

Need Your Help

Getting parent form name of a form

c# winforms

I have a Form (form3)which could be opened from two other forms. Form1 and Form2.

How to split a generic void array into parts.c

c arrays void memcpy memmove

as a beginner in C, I am struggling with an obscure problem and because I couldn't find a solution to this particular problem I want to ask you the following:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.