Row Number Function in MySQL | Important SQL Interview Question

[@sumitmittal07]

Checkout the Big Data course details here: trendytech.in/?referrer=youtube_sql15

[@hafmouss584]

Link does work

[@Nikhil-qi4oz]

Please continue SQLseries

[@pridename2858]

Excellent learning...getting so easy to understand and learning of SQL's most clever tricks....Thank you sir

[@piruanimesh2443]

Thank you so much sumit sir...Its quite easy to understand... Previously I had seen several vdo's but I can't get the actual purpose of using row_number & over clause... Some of the lectures they used the terms window - window which is quite tough to understand it... Thank you so much sir🙏🏻🙏🏻🙏🏻

[@sudarshanmhaisdhune1039]

Simple, smooth & very much practical example & tutorial.

[@saiakankshareddy8722]

thank you so much sir .... this video gave me nice clarification on row number ... your way of explaination is simply awesome ... and very helpful in understanding the topic .. :)

[@hafmouss584]

Thank you so much Sir, you explain better than many paid training and school

[@vikasgadhe7620]

thanks for such a wonderful tutorial.
I have been following your content for a long time.

[@slyroy7562]

Thank Q sir..
If it is possible can you make one video related to Date functions and some scenarios

[@krushnabelokar5783]

Very nice and cristal clear explanation sir of the row function appreciate your work. Congratulations you got one subscriber 🎉 keep it up sir 👍

[@Akashsingh-re9dk]

select * from employee order by salary desc limit 4,1; One from many possible solution said by sumit sir to fetch the 5th highest salary. This querry too I got to know well from your sql sessions.

[@vamshigabbeta2036]

But this doesn't show rownumber 5
It's gives only 5th highest salary

[@piruanimesh2443]

You can calculate the 5th highest salary in different ways
1) by using order by clause with desc & then apply limit fn
2) u can get it by using row_number and over clause also...

[@sudarshanmhaisdhune1039]

To get 5th highest salary from dataset, It should be like:
SELECT * FROM (select * from employee order by salary desc limit 5 offset 4) temptable LIMIT 1;

[@GauravSharmagvs]

As always Excellent tutorial. Thanks a lot for sharing this video.

[@Sahel219]

Very helpful. Thank you!

[@sibghaBiz]

you nailed it

[@kmyamuna6719]

Hi Sumit sir, one doubt here with partition by. There are 2 persons with same salary for banglore location. Is there any way to fetch both the person details for banglore location as there are multiple persons with the same highest salary

[@ningus8522]

thank u
sir...

[@muthu_vrn]

Fantastic

[@udayptp]

Grt lesson on row number, I have one question if I want to assign row num as lets say 1 to all emp. Whose salary is 20000/- then how can you modify it? Here in your query if two emp are earning 20000/- then row num is assigning 1 and 2 but i want same row num for both as salary is same of both. I hope you're getting my confusion??

[@sumitmittal07]

we will learn Rank and Dense Rank in upcoming sessions. your question will be answered there.

[@brijesh0808]

@3:30 Sir, for me it is working even without sepcifying order by

[@tejasbavkar2811]

Thank you sir👍

[@kryurr]

Hello Sir. Thank you for an excellant tutorial
Please help me with following where we were finding 5th highest salary using -
select * from ( select name , salary , row_number() over (order by salary desc) as key from emp ) temptable where rownum=2
It says "NO DATA FOUND". Could you please help ?
- Keyur

[@varuntirupati2566]

Hi Sir, Thanks for the detailed explanation. I have a doubt, Can't, we use the row_number() without order by clause? or is it like we shouldn't use the row_number() function without partition by or order by clauses in the over Clause?

[@vaibhavverma1340]

order by is mandatory whereas partition by optional for all the windowing function

[@arvindkumarsingh8476]

Incase of same salary in few rows, the row_number approach will fail. Right ?

[@anuradhadhere4306]

Pls make videos on PLSQL

[@itsall5180]

why for bangalore 1 record, it should show 2 records as bangalore holds two 1st rank right ??

[@swadhinsonwane4185]

Sir please explain the temptable concept

[@SrinivasMamidi-g2b]

we should give any alias name for that rownumber subquery , or else it will not allow u to fetch details from that, i suggest you to pls try code without giving name and u will come to know

[@veereshk6065]

Lets assume the order by column is having NULL values then How will the rum_number() assign the values ?
I have the same question for RANK and DENSE_Rank as well ?

[@rakhikumari190]

please cover 177 no leetcode question

[@TrueRDX]

sir please also give use the table so dont need to write the query;

[@ShivamSharma-ou1mz]

More correct way will be using Dense Rank function instead of Row Number for calculating Nth Salary

[@sumitmittal07]

Yes.. that we will cover in next section on how to optimize this solution

[@SrinivasMamidi-g2b]

Yes, with Dense_rank we will get all the employess whose having same salary

[@balramlowanshi3819]

Pls send notes Sir

[@sumitmittal07]

Will try adding the link to it in the description section.

[@yashdhas8202]

please take regular expressions

[@sumitmittal07]

Yes definitely :)

[@abhaysagar935]

Samajh nhi aa raha

[@balajibalasubramaniam8349]

plsql

[@abhaysagar935]

I got confusing 😭😭

[@learner-xu5tw]

CREATE TABLE employee (
firstname varchar(20),
lastname varchar(20),
age int,
salary int,
location varchar(20)
);
INSERT INTO employee VALUES ('sachin', 'sharma', 28, 10000, 'bangalore');
INSERT INTO employee VALUES ('shane', 'warne', 30, 20000, 'bangalore');
INSERT INTO employee VALUES ('rohit', 'sharma', 32, 30000, 'hyderabad');
INSERT INTO employee VALUES ('shikhar', 'dhawan', 32, 25000, 'hyderabad');
INSERT INTO employee VALUES ('rahul', 'dravid', 31, 20000, 'bangalore');
INSERT INTO employee VALUES ('saurabh', 'ganguly', 32, 15000, 'pune');
INSERT INTO employee VALUES ('kapil', 'dev',34, 10000, 'pune');