SQL Server QUALIFY: Doesn’t Exist, Do THIS Instead

SQL Server has many useful window functions available to us that can make the task of gathering aggregate/ranking/offset data very easy.

One tool that you might have heard of is the QUALIFY clause. This tool can be used to filter the result of a window function.

The problem is that SQL Server does not have a QUALIFY clause. This is a tool that is available in other database management software, but not Microsoft SQL Server.

In this brief tutorial, we’ll discuss how QUALIFY normally works, then show you what to do in SQL Server to get the same results.

QUALIFY filters the result of a window function

Let’s create some data to work with. We’ll create three tables: Customers, Products, and Orders.

CREATE TABLE Customers(
CustID int IDENTITY(50,5) NOT NULL,
FirstName varchar(20) NULL,
LastName varchar(20) NULL
)

CREATE TABLE Products(
ProductID int IDENTITY(20,2) NOT NULL,
ProductName varchar(20) NULL,
Price decimal(5, 2) NULL
)

CREATE TABLE Orders(
OrderID int IDENTITY(100,10) NOT NULL,
CustID int NULL,
ProdID int NULL,
Qty tinyint NULL,
Orderdate datetime NULL
)

INSERT Customers (FirstName, LastName) 
VALUES 
('Joshua', 'Porter'),
('Andrew', 'Bluefield'),
('Jack', 'Donovan'),
('Cindy', 'Thatcher'),
('Gordon', 'Acres'),
('Gretchen', 'Hamilton')

INSERT Products (ProductName, Price) 
VALUES 
('Large Bench', 198.00),
('Small Bench', 169.40),
('Coffee Table', 220.00),
('Side Tables', 265.20),
('Coat Rack', 45.00)

INSERT Orders (CustID, ProdID, Qty, Orderdate) 
VALUES 
(55, 22, 1, '2021-06-01'),
(60, 28, 2, '2021-06-06'),
(75, 26, 1, '2021-06-13'),
(50, 20, 1, '2021-07-01'),
(55, 28, 1, '2021-07-06'),
(65, 24, 1, '2021-07-14'),
(55, 26, 1, '2021-07-18'),
(50, 26, 1, '2021-07-24'),
(70, 24, 1, '2021-08-06'),
(70, 26, 1, '2021-08-06'),
(70, 22, 3, '2021-09-01')

What if we wanted to rank our customers according to how much income we have made from them? We could use the handy RANK window function to see that result set:

SELECT C.CustID, SUM(O.Qty*P.Price) AS CustomerTotal, 
RANK() OVER (ORDER BY SUM(O.Qty*P.Price) DESC) AS BuyerRank
FROM Customers AS C
INNER JOIN Orders AS O
ON C.CustID = O.CustID
INNER JOIN Products AS P
ON O.ProdID = P.ProductID
GROUP BY C.CustID

Here’s the output:

Not sure how the RANK window function works? Check out the full tutorial:

SQL Server RANK: A window function you should know

Now, what if we wanted to filter this result set to only show us the #1 best-selling customer. That is, the customer who has made us the most income.

We would basically be looking for the row where our BuyerRank column is equal to 1.

To get that information using QUALIFY, we would change the query to something like this:

SELECT C.CustID, SUM(O.Qty*P.Price) AS CustomerTotal 
FROM Customers AS C
INNER JOIN Orders AS O
ON C.CustID = O.CustID
INNER JOIN Products AS P
ON O.ProdID = P.ProductID
GROUP BY C.CustID
QUALIFY RANK() OVER (ORDER BY SUM(O.Qty*P.Price) DESC) = 1

But again, we can’t do this in SQL Server.

What we can do in SQL Server is simply put our main query into a table expression, such as a derived table or a CTE. Let’s put the query in a derived table:

SELECT * FROM
(
SELECT C.CustID, SUM(O.Qty*P.Price) AS CustomerTotal, 
RANK() OVER (ORDER BY SUM(O.Qty*P.Price) DESC) AS BuyerRank
FROM Customers AS C
INNER JOIN Orders AS O
ON C.CustID = O.CustID
INNER JOIN Products AS P
ON O.ProdID = P.ProductID
GROUP BY C.CustID
) AS TheRankingQuery

So now, if we want to see the customer with a rank of 1, we just need to introduce a WHERE clause in the outer query:

So simple!

Next Steps:

Leave a comment if you found this tutorial helpful!

Need to learn a-thing-or-two about window functions in SQL Server? Check out the full beginner-friendly tutorial here:

SQL Server Window Functions: An introduction for beginners

Or better yet, you can download the full FREE Ebook on window functions:

FREE Ebook on SQL Server Window Functions!

This FREE Ebook discusses absolutely everything you need to know about window functions in SQL Server. This eBook will definitely be a great resource for you to reference throughout your career as a data professional. Get it today!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!