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,
UnitPrice
FROM Products
ORDER BY UnitPrice DESC— 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.