RANK, DENSE_RANK, ROW_NUMBER SQL Analytical Functions Simplified

Рет қаралды 88,847

Ankit Bansal

2 жыл бұрын

In this video we will understand very important concept of SQL analytical functions RANK, DENSE_RANK, ROW_NUMBER.

Пікірлер: 213

@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 5 ай бұрын

Thanks

@mohammadsaad7412 18 күн бұрын

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🙏🙏

@niki2098 8 ай бұрын

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.

@srikantjena4532 2 жыл бұрын

Absolutely good to watch with simpler explanations :-)

@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.

@arunabhaghoshal2381 2 жыл бұрын

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

@tirthshah8293 Ай бұрын

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

@ujjwalkumari5690 Жыл бұрын

Great explained! looking forward for more videos

@mohitupadhayay1439 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Thank you 😊

@rasikamahadeshwar8097 Күн бұрын

Very good explanation.

@palakjain8320 Жыл бұрын

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

@sakshitangri7600 2 жыл бұрын

Simply amazing. Thank you so much

@nagasp882 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Glad you liked it 😊

@sunilmaharana6864 2 жыл бұрын

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 2 жыл бұрын

Glad it was helpful!

@anishdeshpande395 Жыл бұрын

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

@karangoyal3747 2 жыл бұрын

Thanks. please continue the good work.

@ektakumari4496 Жыл бұрын

Hats off to your content👌👌

@dataworksstudio 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Thanks Amar for watching 🙂

@sapnanimbalkar1210 9 ай бұрын

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

@skillup638 Жыл бұрын

💎 videos glad I founded it on KZfaq

@nainarahangdale1261 Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Glad you liked it!

@manojahlawat2881 4 ай бұрын

short and good explanation in this video

@RiffswithMohit Жыл бұрын

bhai thanks bht sahi and ez way me samjhaya apne

@sushantdalela7350 2 ай бұрын

Good work bro well explained

@mohit231 2 жыл бұрын

Great video

@gopavarambavithra3025 3 күн бұрын

Nicely explanation

@akshaychoudhary5770 2 жыл бұрын

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

@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

@nirmalbisht9710 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Thank you 😊

@kneelakanta8137 Жыл бұрын

Same question asked in my yesterday interview 👍👍

@jyothieswari2561 6 ай бұрын

Great explanation sir....tq

@ankitbansal6 6 ай бұрын

Keep watching

@aish7711 2 жыл бұрын

Clear explanation. waiting for next video

@ankitbansal6 2 жыл бұрын

Thank you 😊

@pondarajagadeesh4987 10 ай бұрын

Very good explanation, short and concise

@ankitbansal6 10 ай бұрын

Glad it was helpful!

@gayatrikumbhare8530 3 ай бұрын

well explained 💯💯

@varunl6948 7 ай бұрын

Thanks for this!

@sajidulahmed3354 7 ай бұрын

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

@ankitbansal6 7 ай бұрын

Thanks a ton

@arunjadad 3 ай бұрын

Well explained sir

@aakrititimilsina7662 7 ай бұрын

Very nicely explained!

@ankitbansal6 7 ай бұрын

Glad you liked it

@sarojsahoo8763 Жыл бұрын

Nice explanation

@dyingimmortal 9 ай бұрын

Great video Ankit. Very nicely executed with all three functions running simultanesouly and that brings the difference so accurately. Great job.

@ankitbansal6 9 ай бұрын

Glad you liked it!

@tapangshu Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Most welcome!

@zohebakber5056 2 жыл бұрын

nicely explained, bro

@ankitbansal6 2 жыл бұрын

Thanks bro 😊

@shubhipayal4073 9 ай бұрын

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

@ankitbansal6 9 ай бұрын

Glad to hear that😊

@anguraj1401 Жыл бұрын

Thanks Ankit

@amchourasia 11 ай бұрын

Thanks for the video!

@ankitbansal6 11 ай бұрын

You're welcome!

@bhaveshrathod9898 Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Most welcome!

@yuktiagrawal8606 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Thank you 😊

@hammadahmed1659 2 жыл бұрын

Thanks Sir, very nicely explained..

@ankitbansal6 2 жыл бұрын

Most welcome

@MrAsgar123 Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Most welcome!

@rohitrai4811 Жыл бұрын

Appreciated bro 👍

@ankitbansal6 Жыл бұрын

🤝

@minute_syntax Жыл бұрын

great video

@BabyMeraLearn 8 ай бұрын

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

@ankitbansal6 8 ай бұрын

Glad you enjoyed it!

@vishnujatav6329 Жыл бұрын

Really helpful

@ankitbansal6 Жыл бұрын

Glad it helped👍

@samthomas7686 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Thank you 😊

@annasagaramgurumahendrarao6285 2 жыл бұрын

U explained very well

@ankitbansal6 2 жыл бұрын

Thank you 😊

@shashikiran6859 2 жыл бұрын

Great Explanation

@ankitbansal6 2 жыл бұрын

Thank you 😊

@blackjack4667 Жыл бұрын

Thanks Bhaiya

@Ashis_Kumar90 Жыл бұрын

Clear and Crisp

@naveenkumarp9874 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

👏

@nehaarora8754 2 жыл бұрын

Good one Baba ji :)

@ankitbansal6 2 жыл бұрын

🙂🙏

@arunsinghkachhwaha8286 Жыл бұрын

Great Learning with you Ankit😊

@ankitbansal6 Жыл бұрын

It's my pleasure😊

@simranjeetsingh4101 Жыл бұрын

Thanku ♥️

@ArtistJatinRana 6 ай бұрын

Thankyou sir

@ssaaurabh456 Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Awesome

@nabiltahwildar7931 Жыл бұрын

Thank You Sir, Clear understood✌👌

@ankitbansal6 Жыл бұрын

Glad to hear that

@SHUBHADIP1 Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Glad you liked it

@adityasahu8626 Жыл бұрын

very good explanation 😊

@ankitbansal6 Жыл бұрын

Glad you liked it

@veereshsaliyan6007 10 ай бұрын

Thanks alot sir✨

@ankitbansal6 10 ай бұрын

Most welcome

@LogicQuest 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

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

@ankush_in_sync5998 15 күн бұрын

Thank You.

@meghagupta8664 Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Keep watching

@AT-dn6gd Жыл бұрын

JUST WOW

@niyatishah9020 5 ай бұрын

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?

@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

@suman3316 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Sure bro 🙂

@DataRevolution10 2 жыл бұрын

Nice video.

@ankitbansal6 2 жыл бұрын

Thank you 😊

@rahulyeole6411 2 жыл бұрын

Nice video sir please upload more videos with interview questions

@ankitbansal6 2 жыл бұрын

Sure

@jaydeeppatidar4189 2 жыл бұрын

Hi Sir, could you please let us know how much nosql/mongodb we should know to get placed in product based companies. Thank you in advance.

@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

@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.

@sandeepmohanty6088 Жыл бұрын

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

@parteekranga3238 Жыл бұрын

All I want to say is, I LOVE YOU MAN

@ankitbansal6 Жыл бұрын

🙏

@RoopmathiGunnaubcmba 11 ай бұрын

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

@ankitbansal6 11 ай бұрын

There are many in my complex SQL problems playlist

@sureshdarla5540 3 ай бұрын

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

@PraveenSingh-no8ol 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Okay

@tarunaggarwal4995 5 ай бұрын

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 😛

@veerjisingh6401 2 жыл бұрын

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 2 жыл бұрын

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 )

@roopasp1860 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

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

@roopasp1860 2 жыл бұрын

@@ankitbansal6 thankyou

@sreevidyagireesh Жыл бұрын

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

@ankitbansal6 Жыл бұрын

Same performance

@rishinagar199 10 ай бұрын

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

@adityabaha 2 жыл бұрын

Can I say this as a general thumb rule to remember this --> If I want to see the 'measure' (salary) for top n measure (salary), I will do dense_rank, If I want to see the 'dimension' (emp_name) for top n measure (salary, I will do rank()

@ankitbansal6 2 жыл бұрын

Absolutely

@jyotiaditya7686 2 жыл бұрын

@@ankitbansal6 Thank You, You are doing a GREAT help for folks like us. Please Keep It Up !! Also, insert data scripts that you share are really really helpful to do hands on Practice 🙏🏻

@jasleenkaur-cw2fw Жыл бұрын

@@jyotiaditya7686 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);

@poornimasaravanan9389 2 ай бұрын

Cant we use group by clause?

@romideep4463 2 жыл бұрын

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. 2 жыл бұрын

Yes, for getting highest salary we should use dense_rank.

@shwethaa937 2 жыл бұрын

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

@ankitbansal6 2 жыл бұрын

Order by will take care of sorting

@proandy4448 18 күн бұрын

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 18 күн бұрын

This is the only video in Hindi on my KZfaq channel

@AbhishekGupta-ij3pn Жыл бұрын

Good morning Sir where we can practice online SQL ?

@yatinshekhar787 Жыл бұрын

1/122

@akashwatar6633 Жыл бұрын

I am planning to complete all the SQL videos created by you in order to learn SQL. I will post a comment on each video and like it as a checklist for completed videos, starting from the beginning.

@ankitbansal6 Жыл бұрын

Awesome. Keep going 💪