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:
https://youtu.be/H6OTMoXjNiM

T-SQL Querying
https://www.amazon.com/gp/product/0735685045/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735685045&linkCode=as2&tag=beardeddev05-20&linkId=e93b0f7483b1fa06aca5a3aec4f7764b

T-SQL Fundamentals
https://www.amazon.com/gp/product/150930200X/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=150930200X&linkCode=as2&tag=beardeddev05-20&linkId=70aa2ffa3e84cab66cb964ca1e9cee62

Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
https://www.amazon.com/gp/product/0735658366/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0735658366&linkCode=as2&tag=beardeddev05-20&linkId=b88746aea666c9f98da147e70b4491ca

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

Видео SQL Tutorial - Window Functions - Ranking канала BeardedDev