Let’s try to answer this question.
The “order by” clause allows the expressions. The expression can be simple, constant, variable, column, and so on. Simple expressions can be organized into complex ones.
What if you talk to query optimizer using the expression’s language? For example, try to use the subquery as an expression:
SELECT ROW_NUMBER () OVER (ORDER BY (SELECT orderID FROM RankingFunctions)) AS rowNum, orderID
FROM RankingFunctions;
No, you can’t bypass the parser. You will get an error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
O-o-o-p-s, here’s the hint! The expression (or in our case, the subquery) has to produce a single value.
This should work:
SELECT ROW_NUMBER () OVER (ORDER BY (SELECT MAX(OrderID) FROM RankingFunctions)) AS rowNum, orderID
FROM rankingFunctions;
Bingo! That query is working exactly as you wanted — no Sort operator has been used.
Now you can write an expression in the “order by” clause that returns a single value, forcing the query optimizer to refrain from using a sort operation.
By the way, the solutions in Listing 2 worked because the integer values in computed columns have been duplicated in all the rows and for that reason were considered a single value.
Here are some more examples of expression usage in an “order by” clause (Listing 3):
— Listing 3. Using an expression in an ORDER BY clause.
SELECT ROW_NUMBER () OVER (ORDER BY (SELECT 1 FROM sysobjects WHERE 1<>1)) AS rowNum, orderID
FROM RankingFunctions;
GO
SELECT ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS rowNum, orderID
FROM RankingFunctions;
GO
DECLARE @i as bit;
SELECT @i = 1;
SELECT ROW_NUMBER () OVER (ORDER BY @i) AS rowNum, orderID
FROM RankingFunctions;
Figure 3 shows the execution plans for the queries in Listing 3.
RANK(), DENSE_RANK() and NTILE() Functions with Expressions in an ORDER BY Clause
Before we move forward, we should check the correctness of the solutions for the rest of the ranking functions.
Let’s create a few duplicates in the RankingFunctions table and start testing the RANK() and DENSE_RANK() functions:
— Listing 4. RANK() and DENSE_RANK() functions with expressions in an ORDER BY clause.
— Create duplicates in table RankingFunctions.
INSERT INTO RankingFunctions VALUES(11);
INSERT INTO RankingFunctions VALUES(4);
INSERT INTO RankingFunctions VALUES(4);
GO
— Query 1: (ORDER BY orderID).
SELECT RANK () OVER (ORDER BY orderID) AS rankNum,
DENSE_RANK () OVER (ORDER BY orderID) AS denseRankNum,
orderID
FROM RankingFunctions;
GO
— Query 2: (ORDER BY expression).
SELECT RANK () OVER (ORDER BY (SELECT 1)) AS rankNum,
DENSE_RANK () OVER (ORDER BY (SELECT 1)) AS denseRankNum,
orderID
FROM RankingFunctions;
GO
If you check the execution plans (see Figure 4), you will find that the first query in Listing 4 requires a lot of resources for sorting. The second query doesn’t have a Sort operator. So the queries behave as expected.
