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/gp/product/073...
T-SQL Fundamentals
www.amazon.com/gp/product/150...
Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions
www.amazon.com/gp/product/073...
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

[@OldPick-Unix-dude-pb9jg]

Great video. Thanks for keeping these out there for us to refer to as we find ourselves in a jam.

[@rajatb22]

I have gone through many videos on UA-cam and also some paid content on many different sites but hands-down this is the best series on window functions.

[@BeardedDevData]

Thanks so much.

[@revathisubhasht]

Finally, I understood the window functions easily...Thanks alot

[@BeardedDevData]

Glad I could help.

[@houstonvanhoy7767]

Step by step - the only way to learn. Thank you, Brad.

[@pondmarketplaces]

Every single video you can hear his hands rubbing against each other while he talks, but I'm not complaining. He's giving me free knowledge.

[@abhisheksengupta4314]

I cant hear it :D

[@GrumpySalmon]

Now I started to notice it.. Thanks mate :)

[@bradbyington6662]

Excellent and palpable presentation of something that sounds abstract in thought. These videos are very helpful!

[@BeardedDevData]

Thanks for the positive feedback

[@brolino11]

This should have waaaay more views

[@priyadhandev]

I found window function to be complex to understand but you have explained them very beautifully!! Thanks for the short videos.

[@houstonvanhoy7767]

4:02 and 6:05
( ) are parentheses.
[ ] are brackets.
{ } are braces or curly brackets.

[@mostinho7]

Done thanks for great video, took notes

[@Jalalx]

I am wondering why this channel doesn't get the attention it deserves?

[@BeardedDevData]

I wonder that myself, will start producing some new videos shortly.

[@houstonvanhoy7767]

@@BeardedDevData See my comments. Also, search for "how to promote a youtube channel" and see what makes sense to you. THANK YOU FOR THE CONTENT. 👍

[@nickarrigo5540]

well bloody done BD! I'm learning a lot from you.

[@BijouBakson]

Oh this helped make it so easy to understand. Thank you BeardedDev!

[@ten2the6]

You sir are an amazing teacher!

[@pallaviiii847]

I'm glad I found this channel. The videos are very helpful. You don't seem to be active these days, hope you are doing great.

[@BeardedDevData]

Hi Krishna, I'm well thank you, there will be a lot of content coming this year including more on Azure and Power BI

[@sabaamanollahi5901]

thanks for the fantastic video

[@nateslaptop8991]

Extremely helpful video (and playlist), kudos!

[@BeardedDevData]

Thanks so much, I will have some new videos up over the next couple of weeks

[@ElvisSCL]

Great explanation!

[@fernandocamargo2637]

Another great explanation video! I like the way you teach, mate! Tks

[@BeardedDevData]

Thanks so much, new videos coming early next year

[@jeevank01]

Keep up the good work!

[@kaanchii123]

THANK YOU!

[@lemmiTry]

Nice short videos well explained. Thanks!

[@BeardedDevData]

Thanks very much

[@mcflycalifornia]

Love your channel thanks so much!

[@mcflycalifornia]

With help of your videos I cleared my technical round yay :D
They asked window functions.

[@cavin3401]

Excellent fabulous awesome lecture 🙌🙌

[@hassanOnYoutube]

Love from Pakistan ! I'm learning a lot from you, Stay blessed.

[@wildslots2373]

Thanks

[@nishantkumar-lw6ce]

Hi, thanks for the amazing videos. Would be really helpful if we could have session on self joins.

[@BeardedDevData]

Thanks for the feedback, I will post a video on self joins next week and let you know when it’s online.

[@nishantkumar-lw6ce]

Thank you so much. One suggestion : I know that your videos are amazing. Please consider different use cases of self joins with examples such as hierarchical use case, sequential use case and network graph for example. Please consider simple to complex use cases :) Waiting for your videos next week! Thanks again :)

[@kartiksingh2288]

Thank you so much for sharing this. Can you please share table script?

[@ssurekha1]

Could we get access to the Table that you're using in this Video?

[@markwade4048]

I am going to watch the entire series of BeardedDev SQL tutorials as they are so good. Is there an easy way to save them all in one go without having to do it individually for each tutorial?

[@BeardedDevData]

Hi Mark, there is a playlist on my channel called SQL Tutorials that contains majority of my videos, you can watch that, also I am starting to work on new content now, if there are any particular areas you'd like covering please do let me know.

[@markwade4048]

@@BeardedDevData yes, go it. Thanks, saves me rooting round in YT for them!

[@BeardedDevData]

Here is the link: SQL Tutorials: ua-cam.com/play/PLgR-BOYibnN0qAHzuVOLPNDDM3JfB7o5_.html

[@imranyasin7537]

Thanks for the great videos! Can you please share the data that you are using in your tutorials?

[@BeardedDevData]

Hi, I have started to incorporate this in my newer videos

[@affafa100]

At 4:53, order by inside over clause is ordering by sales total? Can't understand that.

[@BeardedDevData]

Sales_Total is a column in the table.

[@portiseremacunix]

407 all positive, 0 negativ. 2020/11/29

[@mostafazaki8451]

I can’t change video quality from mobile app this first time to face it

[@BeardedDevData]

Appreciate that, I have used some new software in my latest videos that allows zooming.

[@indirameduri9853]

Too small font! :(

[@BeardedDevData]

Appreciate that, I will be increasing the font size and zooming in, in future videos.

[@indirameduri9853]

BeardedDev really good explanation of window function.