Row Number Function in MySQL | Important SQL Interview Question

𝐖𝐚𝐧𝐭 𝐭𝐨 𝐌𝐚𝐬𝐭𝐞𝐫 𝐒𝐐𝐋? 𝐋𝐞𝐚𝐫𝐧 𝐒𝐐𝐋 𝐭𝐡𝐞 𝐫𝐢𝐠𝐡𝐭 𝐰𝐚𝐲 𝐭𝐡𝐫𝐨𝐮𝐠𝐡 𝐭𝐡𝐞 𝐦𝐨𝐬𝐭 𝐬𝐨𝐮𝐠𝐡𝐭 𝐚𝐟𝐭𝐞𝐫 𝐜𝐨𝐮𝐫𝐬𝐞 - 𝐒𝐐𝐋 𝐂𝐡𝐚𝐦𝐩𝐢𝐨𝐧𝐬 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫!

"𝐀 8 𝐰𝐞𝐞𝐤 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐝𝐞𝐬𝐢𝐠𝐧𝐞𝐝 𝐭𝐨 𝐡𝐞𝐥𝐩 𝐲𝐨𝐮 𝐜𝐫𝐚𝐜𝐤 𝐭𝐡𝐞 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰𝐬 𝐨𝐟 𝐭𝐨𝐩 𝐩𝐫𝐨𝐝𝐮𝐜𝐭 𝐛𝐚𝐬𝐞𝐝 𝐜𝐨𝐦𝐩𝐚𝐧𝐢𝐞𝐬 𝐛𝐲 𝐝𝐞𝐯𝐞𝐥𝐨𝐩𝐢𝐧𝐠 𝐚 𝐭𝐡𝐨𝐮𝐠𝐡𝐭 𝐩𝐫𝐨𝐜𝐞𝐬𝐬 𝐚𝐧𝐝 𝐚𝐧 𝐚𝐩𝐩𝐫𝐨𝐚𝐜𝐡 𝐭𝐨 𝐬𝐨𝐥𝐯𝐞 𝐚𝐧 𝐮𝐧𝐬𝐞𝐞𝐧 𝐏𝐫𝐨𝐛𝐥𝐞𝐦."

𝐇𝐞𝐫𝐞 𝐢𝐬 𝐡𝐨𝐰 𝐲𝐨𝐮 𝐜𝐚𝐧 𝐫𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐟𝐨𝐫 𝐭𝐡𝐞 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 -

𝐖𝐚𝐧𝐭 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐁𝐢𝐠 𝐃𝐚𝐭𝐚 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫?
𝐜𝐡𝐞𝐜𝐤𝐨𝐮𝐭 𝐭𝐡𝐞 𝐛𝐢𝐠 𝐝𝐚𝐭𝐚 𝐜𝐨𝐮𝐫𝐬𝐞 𝐝𝐞𝐭𝐚𝐢𝐥𝐬

I have trained over 20,000+ professionals in the field of Data Engineering in the last 5 years.

Row Number Function in MySQL | Important SQL Interview Question

In this video we will talk about how to use a row_number function in SQL and its industry use case.

Remember -
We should be using the order by clause
We can also use the partition by - but its optional
the row number starts from 1 for every partition

Do Like, Comment & Subscribe :)

𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥

#SQL #RDBMS #database #bigdata #dataengineering

[pridename]

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

[sudarshanmhaisdhune]

Simple, smooth & very much practical example & tutorial.

[piruanimesh]

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

[learner-xutw]

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');

[hafmouss]

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

[saiakankshareddy]

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 .. :)

[Akashsingh-redk]

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.

[brijesh]

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

[vikasgadhe]

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

[GauravSharmagvs]

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

[krushnabelokar]

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

[slyroy]

Thank Q sir..

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

[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

[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??

[kmyamuna]

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

[varuntirupati]

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?

[arvindkumarsingh]

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

[ShivamSharma-oumz]

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

[itsall]

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

[TrueRDX]

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