RANK, DENSE_RANK, ROW_NUMBER SQL Analytical Functions Simplified

[@jasleenkaur-cw2fw]

Clear explanation 💯 For those asking for dataset: create table employee( emp_id int, emp_name varchar(20), dept_id int, salary int ); insert into employee values(1,'Ankit',100,10000); insert into employee values(2,'Mohit',100,15000); insert into employee values(3,'Vikas',100,10000); insert into employee values(4,'Rohit',100,5000); insert into employee values(5,'Mudit',200,12000); insert into employee values(6,'Agam',200,12000); insert into employee values(7,'Sanjay',200,9000); insert into employee values(8,'Ashish',200,5000);

[@shoaibraza5532]

Thanks

[@mohammadsaad7412]

u can insert using single insert query create table employee( emp_id int, emp_name varchar(20), dept_id int, salary int ); INSERT INTO employee (emp_id, emp_name, dept_id, salary) VALUES (1, 'Ankit', 100, 10000), (2, 'Mohit', 100, 15000), (3, 'Vikas', 100, 10000), (4, 'Rohit', 100, 5000), (5, 'Mudit', 200, 12000), (6, 'Agam', 200, 12000), (7, 'Sanjay', 200, 9000), (8, 'Ashish', 200, 5000);

[@sandeepreddykaduduri799]

This Explanation Blew my mind..!! From Scaring.. To Caring..on SQL. its just because of u Guruji🙏🙏

[@nagasp882]

Very good explanation, short and concise. It's the only video i would want to watch before my interview for quick revision.

[@ankitbansal6]

Glad you liked it 😊

[@niki2098]

Very good explanation and easier to understand. Earlier I'm unable to understand difference between these 3 functions now I'm clear. Thank you so much for this video.

[@gauravpanchariya]

Very aptly explained. I was asked this window sql question in Gainwell interview. I couldn't answer as I were not aware of it. I searched for these functions and saw your video. It is very easily explained and very precise to the core.

[@sanchitavichale6737]

I didn't understand analytical function until I see this video. Thank you for explain in simple word to understand. 👍

[@ankitbansal6]

You are most welcome

[@srikantjena4532]

Absolutely good to watch with simpler explanations :-)

[@tirthshah8293]

Explained to the point. Cleared the doubt related to the difference between these all the functions.

[@Maharanasunil01]

I was looking for videos on Advance SQL but was still struggling with understanding the concepts. I really liked the explanation. It's time to practice a few questions ;) Thank you, can't wait for the next video :)

[@ankitbansal6]

Glad it was helpful!

[@nirmalbisht9710]

Enjoy very topic you taught sir .. You have amazing teaching skill even these hard topic you cover very easily thank U

[@ankitbansal6]

Thank you 😊

[@BabyMeraLearn]

Very nicely explained and in easy to understand. Great job!

[@ankitbansal6]

Glad you enjoyed it!

[@pondarajagadeesh4987]

Very good explanation, short and concise

[@ankitbansal6]

Glad it was helpful!

[@bhaveshrathod9898]

Really very good explanation sir! Thank you very much:)

[@ankitbansal6]

Most welcome!

[@shubhipayal4073]

Thankyou .After lots of watching videos finally my concept is cleared here.

[@ankitbansal6]

Glad to hear that😊

[@nainarahangdale1261]

That's the simplest and most understandable explanation :) Thank You !!

[@ankitbansal6]

Glad you liked it!

[@aakrititimilsina7662]

Very nicely explained!

[@ankitbansal6]

Glad you liked it

[@sapnanimbalkar1210]

Enhancing clarity leads to an interest in learning. Thank you Ankit.. !

[@haripriyaramakrishnan6316]

All are amazing videos.Very clear experience. It is very useful to refresh the SQL before interview.I am working as AVP in citicorp.Thank you very much Recently I used ur amazing videos to clear interview. Thank you very much.

[@anishdeshpande395]

Understood teh concept in a very simple way. Thank you :)

[@dataworksstudio]

An absolute no-nonsense guide to window functions! Thanks bhaiyya ❤️

[@ankitbansal6]

Thanks Amar for watching 🙂

[@arunabhaghoshal2381]

Thanks very much for the clarity and in detailed explanation of the concepts !

[@mohitupadhayay1439]

This is by far the best approach to teaching the windows function. SUBBED right away!

[@ankitbansal6]

Thank you 😊

[@Palak_ki_jhalak1]

Thank you so much for all your videos and explaining in simpler language.All videos are very helpful 😊

[@skillup638]

💎 videos glad I founded it on KZbin

[@akshaychoudhary5770]

neet work. nicely explianed. expecting more such videos. LIKED COMMMENTED & SUBSCRIBED.

[@MrAsgar123]

Thank you so much for your hard work. It's really very useful to understand the Analytical Functions.

[@ankitbansal6]

Most welcome!

[@RiffswithMohit]

bhai thanks bht sahi and ez way me samjhaya apne

[@ssaaurabh456]

hatoff to big brother love from noida ................you cleared concept like piece of cake

[@ankitbansal6]

Awesome

[@hammadahmed1659]

Thanks Sir, very nicely explained..

[@ankitbansal6]

Most welcome

[@karangoyal3747]

Thanks. please continue the good work.

[@sajidulahmed3354]

It's simple and easily understandable. I haven't even understood earlier from the top KZbinrs. Thanks for your efforts. Keep it up!

[@ankitbansal6]

Thanks a ton

[@sakshitangri7600]

Simply amazing. Thank you so much

[@ujjwalkumari5690]

Great explained! looking forward for more videos

[@SHUBHADIP1]

Thank you Ankit ! it was a very simple and clear explanation.

[@ankitbansal6]

Glad you liked it

[@meghagupta8664]

It's very helpful Sir thank you please make more this types of vedio 😊

[@ankitbansal6]

Keep watching

[@abhisheknigam3768]

Amazing explanation.

[@AttitudeAdjestment]

Nice video!

[@naveenkumarp9874]

🥰my doubt is cleared Excellent Explaination about ranks thanks a lot👍

[@ankitbansal6]

👏

[@tapangshu]

Such a simple explanation to these 3 functions. Thanks Ankit :)

[@ankitbansal6]

Most welcome!

[@rasikamahadeshwar8097]

Very good explanation.

[@manojahlawat2881]

short and good explanation in this video

[@kneelakanta8137]

Same question asked in my yesterday interview 👍👍

[@nabiltahwildar7931]

Thank You Sir, Clear understood✌👌

[@ankitbansal6]

Glad to hear that

[@minute_syntax]

great video

[@sureshdarla5540]

thanks for lecture, according to the last example , RANK( ) can't solve for the second highest salary provided by the department . am I right?

[@niyatishah9020]

I found the entire video to be well explained. There is just one thing I need to understand How did you know you had to use aliasing at 8:55?

[@harshavardhanraj3028]

great explanation and the best thing here is using small dataset to simplify concepts in a short span please help me why alias was added at the last to get output i want to visualize how it works.

[@ankitbansal6]

In SQL server it is mandatory to give alias to a sub query

[@arunsinghkachhwaha8286]

Great Learning with you Ankit😊

[@ankitbansal6]

It's my pleasure😊

[@noopurkumari6418]

Very nice videos. It cleared my concept around these functions. Thank you. Please let me know online where i can practise SQL queries.

[@ankitbansal6]

Thank you. You can practice on the DataLemur website.

[@RoopmathiGunnaubcmba]

Thank you Ankit this was very easy to understand. Can you post some more interview questions using RANK(), DENSE RANK() and ROW_number

[@ankitbansal6]

There are many in my complex SQL problems playlist

[@mohit231]

Great video

[@sushantdalela7350]

Good work bro well explained

[@annasagaramgurumahendrarao6285]

U explained very well

[@ankitbansal6]

Thank you 😊

[@jyothieswari2561]

Great explanation sir....tq

[@ankitbansal6]

Keep watching

[@gopavarambavithra3025]

Nicely explanation

[@ektakumari4496]

Hats off to your content👌👌

[@aish7711]

Clear explanation. waiting for next video

[@ankitbansal6]

Thank you 😊

[@rahulyeole6411]

Nice video sir please upload more videos with interview questions

[@ankitbansal6]

Sure

[@veereshsaliyan6007]

Thanks alot sir✨

[@ankitbansal6]

Most welcome

[@adityasahu8626]

very good explanation 😊

[@ankitbansal6]

Glad you liked it

[@psh57]

THANK YOU MENAS A LOT

[@arunjadad]

Well explained sir

[@IndianHacker-hisBest]

Very nice explanation

[@ankitbansal6]

Keep watching

[@vishnujatav6329]

Really helpful

[@ankitbansal6]

Glad it helped👍

[@ashishkumarg5]

Nice explanation

[@gayatrikumbhare8530]

well explained 💯💯

[@proandy4448]

Please can you maintain the English language only. Your content is very helpful & i think it will not benefit the larger audience if you speak any other language. You are a great teacher!!

[@ankitbansal6]

This is the only video in Hindi on my KZbin channel

[@amchourasia]

Thanks for the video!

[@ankitbansal6]

You're welcome!

[@divyadubey6679]

Thank u so much for this video. Please share me link for query using pivot in mysql

[@PraveenSingh-no09ol]

Hi Ankit Bhai, if possible kindly create a video playlist on SQL how much it is required for Analytics domain.

[@ankitbansal6]

Okay

[@parteekranga3238]

All I want to say is, I LOVE YOU MAN

[@ankitbansal6]

🙏

[@priyatiwari234]

clear explanation

[@rohitrai4811]

Appreciated bro 👍

[@ankitbansal6]

🤝

[@suman3316]

apka...pehla subcriber hoo main.....bhaiya ...please bring lots of practical videos used in real time

[@ankitbansal6]

Sure bro 🙂

[@skrandomthings4474]

Informative

[@yuktiagrawal8606]

This is superb... I recently came across to your channel and it is really helpful😊 Teacher's should teach like you..

[@ankitbansal6]

Thank you 😊

[@sreevidyagireesh]

Nice explanation.. I have a doubt performance wise rank or dense rank is better

[@ankitbansal6]

Same performance

[@varunl6948]

Thanks for this!

[@Ashis_Kumar90]

Clear and Crisp

[@veerjisingh6401]

Thanks for explaining in such simple language . I have one doubt regarding the interview question can that be solved by simply using GROUP BY department with max(salary)

[@ankitbansal6]

In that case you will get just max salary for each department but not other columns in the output

[@ritikasood433]

@@ankitbansal6 : Can we use this query to get the highest salary from each department and display all columns, sub-query might not be best option/optimized way, please take a look and advice. select emp_id, emp_name, department_id , salary from employee where salary in (select max(salary) from employee group by department_id )

[@romideep4463]

To get highest salary..i gues you should use dense_rank. Because we need 3rd highest salary we can't fetch from this example which you have given...Am I right???

[@kraminkaverma7837.]

Yes, for getting highest salary we should use dense_rank.

[@zohebakber5056]

nicely explained, bro

[@ankitbansal6]

Thanks bro 😊

[@AT-dn6gd]

JUST WOW

[@samthomas7686]

Great explanation Ankit. Keep on creating similar stuff, all the best!

[@ankitbansal6]

Thank you 😊

[@anguraj1401]

Thanks Ankit

[@devendergusain8974]

thanks sir its good to understand easily in hindi as compare to english

[@ankitbansal6]

Keep watching

[@tarunaggarwal4995]

Nice explanation sir but i have small doubts if we have 2 conditions like salary and joining date and i want look at the salary but if some employees salary equal then he will look joining date and salary both so how we can find the data can you explain 😛

[@ArtistJatinRana]

Thankyou sir

[@roopasp1860]

Thanks Ankit, can you please let me know how we will decide when to use rank(), dense_rank() and row_number()

[@ankitbansal6]

It depends on business use case. There is no thumb rule as such.

[@roopasp1860]

@@ankitbansal6 thankyou

[@kingsleyelijah8461]

create table emp( emp_id int, emp_name varchar(20), department_id int, salary int ); insert into employee values(1,'Ankit',100,10000); insert into employee values(2,'Mohit',100,15000); insert into employee values(3,'Vikas',100,10000); insert into employee values(4,'Rohit',100,5000); insert into employee values(5,'Mudit',200,12000); insert into employee values(6,'Agam',200,12000); insert into employee values(7,'Sanjay',200,9000); insert into employee values(8,'Ashish',200,5000);

[@dishantahuja975]

Thanks for create statement.

[@sriphani4720]

Please pin this comment so that someone who views this video can practice the same while learning. Thank you.

[@shashikiran6859]

Great Explanation

[@ankitbansal6]

Thank you 😊

[@surojeetghosh8389]

hello sir,i almost watch all of your videos and your videos have always given me useful insights.i'm an aspiring data analyst.i know the basic concepts of sql.i just want to know how to go on that next level where i can crack interviews with big companies.thank you

[@ankitbansal6]

Just solve my complex SQL playlist yourself

[@blackjack4667]

Thanks Bhaiya

[@DataRevolution10]

Nice video.

[@ankitbansal6]

Thank you 😊

[@shwethaa937]

wanted to know if the records need to be sorted? or the partition function itself will take care of it ?

[@ankitbansal6]

Order by will take care of sorting

[@ankush_in_sync5998]

Thank You.

[@LogicQuest]

Thanks for these..keep uploading and simplifying these concepts.. I hope to see some python,tableau content soon too.. Hope your channel reaches 100k fast..best wishes ..

[@ankitbansal6]

Thank you so much 😊 I will start Tableau soon. I am thinking should I start with basic tutorials or direct scenario based questions just like SQL. Any suggestions?

[@idhwanibhatt]

@@ankitbansal6 No no start with direct scenario based questions / case study. We are fed with Tutorials.

[@rishinagar199]

Bro ye where clause vala kesey kiya ....mujmey to error show kr raha hai ki 'rnk unknown column in where clause

[@simranjeetsingh4101]

Thanku ♥️

[@sandeepmohanty6088]

Can we use row_number() in this case as row number will be unique and we can select where rownumber=1.