Window Function Basics

Kevin Feasel



Doug Kline has a new series on window functions.  First, he looks at differences between RANK, DENSE_RANK, and ROW_NUMBER:

— Quick! What’s the difference between RANK, DENSE_RANK, and ROW_NUMBER?

— in short, they are only different when there are ties…

— here’s a table that will help show the difference
— between the ranking functions

— note the [Score] column,
— it will be the basis of the ranking

Then, he starts looking at how to build a window function, starting with the OVER clause:

— here’s a simple SELECT statement from the Products table

SELECT ProductName,
FROM Products

— this shows that the highest priced product is Cote de Blaye, productID 38

— but sometimes the *relative* price is more important than the actual price
— in other words, we want to know how products *rank*, based on price

Doug’s entire posts are T-SQL scripts along with embedded videos.

Related Posts

Odd Behavior With Altering Columns

Solomon Rutzky points out a few things which you can unintentionally change when running an ALTER TABLE [tbl] ALTER COLUMN [col] command: If the column is NOT NULL, then not specifying NOT NULL will cause it to become NULLable. The documentation for ALTER TABLE even states: ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable. Let’s see […]

Read More

LISTAGG In Snowflake DB

Koen Verbeeck continues investigating Snowflake capabilities: Since SQL Server 2017, you have the STRING_AGG function, which has almost the exact same syntax as its Snowflake counterpart. There are two minor differences:– Snowflake has an optional DISTINCT– SQL Server has a default ascending sorting. If you want another sorting, you can specify one in the WITHIN GROUP clause. […]

Read More


October 2017
« Sep Nov »