SQL Tutorial - Window Functions - Ranking

Another fantastic SQL Tutorial brought to you by BeardedDev.
If you are new to working with Window Functions check out this video:
• SQL Tutorial - Window ...
T-SQL Querying
www.amazon.com...
T-SQL Fundamentals
www.amazon.com...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
www.amazon.com...
In this video we explore ranking functions available as part of Window Functions:
ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()
This tutorial shows an example of when to use each ranking function and the differences between them.
Window Functions were first introduced in SQL Server 2005 but further enhancements and support was added in SQL Server 2012.
We look at the OVER clause and PARTITION BY.
Window Functions can only be included within SELECT or ORDER BY clauses.
Functions Available:
Aggregate - COUNT, SUM, MIN, MAX, AVG
Ranking - ROW_NUMBER, RANK, DENSE_RANK, NTILE
Offset - FIRST_VALUE, LAST_VALUE, LEAD, LAG
Statistical - PERCENT_RANK, CUME_DIST, PERCENTILE_CONT, PERCENTILE_DIST
Windows Functions also have FRAMES
ROWS
RANGE
Window Functions are a powerful tool within SQL Server and I am excited to bring more videos and tutorials working with Window Functions in the future.
ROW_NUMBER - unique incrementing integers
RANK - same rank for same values
DENSE_RANK - same rank for same values
NTILE - assigns tile numbers based on number of tiles requested
SQL:
SELECT
Sales_Id
, Sales_Total
, ROW_NUMBER() OVER(ORDER BY Sales_Total DESC) AS rownum
, RANK() OVER(ORDER BY Sales_Total DESC) AS rnk
, DENSE_RANK() OVER(ORDER BY Sales_Total DESC) AS dense
, NTILE(3) OVER(ORDER BY Sales_Total DESC) AS ntle
FROM dbo.Sales_2
SELECT
Sales_Id
, NTILE(10) OVER(ORDER BY Sales_Total DESC) AS ntle
FROM dbo.Sales_2