SQL Server window functions

One of the most obvious and useful set of window functions are ranking functions where rows from the data set are ranked accordingly. There are three ranking functions:

ROW_NUMBER()
RANK()
DENSE_RANK()

The difference is easy to remember. For the examples, let’s assume we have this stocks data set.

IF OBJECT_ID('tempdb..#stocks') IS NOT NULL DROP TABLE #stocks;
;With Stocks AS
(
    SELECT 'MSFT' Symbol UNION ALL
    SELECT 'MSFT' Symbol UNION ALL
    SELECT 'MSFT' Symbol UNION ALL
    SELECT 'AAPL' Symbol UNION ALL
    SELECT 'GOOG' Symbol UNION ALL
    SELECT 'GOOG' Symbol UNION ALL
    SELECT 'YHOO' Symbol UNION ALL
    SELECT 'T' Symbol
)
SELECT * INTO #stocks FROM Stocks;
--SELECT * FROM #stocks

ROW_NUMBER()

This will assigns unique numbers to each row within the PARTITION given to the ORDER BY clause. SQL Server require an explicit ORDER BY clause in the OVER() clause for string data type. ORDER BY is not mandatory in monetary columns (INT, DECIMAL, FLOAT …).

SELECT Symbol, ROW_NUMBER() OVER(ORDER BY Symbol) [ROW_NUMBER]
FROM #stocks

RANK()

This behaves like ROW_NUMBER(), except that “equal” rows are ranked the same. If we substitute RANK() from previous query:

SELECT Symbol, RANK() OVER(ORDER BY Symbol) [RANK]
FROM #stocks

As you can see, we have gaps between different ranks. We can avoid those gaps by using following;

DENSE_RANK()

DENSE_RANK() is a rank with no gaps, i.e. it is “dense”. We can write:

SELECT Symbol, DENSE_RANK() OVER(ORDER BY Symbol) [DENSE_RANK]
FROM #stocks

To get a good understanding of these three ranking functions is to see them all in action side-by-side. Run this query

SELECT
    SYMBOL,
    ROW_NUMBER() OVER(ORDER BY Symbol) [ROW_NUMBER],
    RANK() OVER(ORDER BY Symbol) [RANK],
    DENSE_RANK() OVER(ORDER BY Symbol) [DENSE_RANK]
FROM #stocks

Sometimes we don’t have order by column and we simple want to return row numbers using Row_Number function. Here is the same query with changes;

SELECT
    SYMBOL,
    ROW_NUMBER() OVER(ORDER BY Symbol) [ROW_NUMBER],
    RANK() OVER(ORDER BY Symbol) [RANK],
    DENSE_RANK() OVER(ORDER BY Symbol) [DENSE_RANK]
FROM #stocks
SELECT
	SYMBOL,
    ROW_NUMBER() OVER(ORDER BY (SELECT 1)) [ROW_NUMBER],
    RANK() OVER(ORDER BY (SELECT 1)) [RANK],
    DENSE_RANK() OVER(ORDER BY (SELECT 1)) [DENSE_RANK]
FROM #stocks

If you compare this result with earlier, you can see that RANK and DENSE_Rank functions doesn’t like the constant, so the values are 1 in respective columns.

You can use any literal value in Order by clause;

order by (select 0)
order by (select 1)
order by (select null)
order by (select 'test')

The above means that when you are using constant ordering is not performed by query optimizer.

Resources

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15

https://stackoverflow.com/questions/44105691/row-number-without-order-by

Add to favorites