Row Number Function in MySQL | Important SQL Interview Question

Рет қаралды 49,071

2 жыл бұрын

𝐖𝐚𝐧𝐭 𝐭𝐨 𝐌𝐚𝐬𝐭𝐞𝐫 𝐒𝐐𝐋? 𝐋𝐞𝐚𝐫𝐧 𝐒𝐐𝐋 𝐭𝐡𝐞 𝐫𝐢𝐠𝐡𝐭 𝐰𝐚𝐲 𝐭𝐡𝐫𝐨𝐮𝐠𝐡 𝐭𝐡𝐞 𝐦𝐨𝐬𝐭 𝐬𝐨𝐮𝐠𝐡𝐭 𝐚𝐟𝐭𝐞𝐫 𝐜𝐨𝐮𝐫𝐬𝐞 - 𝐒𝐐𝐋 𝐂𝐡𝐚𝐦𝐩𝐢𝐨𝐧𝐬 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫!
"𝐀 8 𝐰𝐞𝐞𝐤 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐝𝐞𝐬𝐢𝐠𝐧𝐞𝐝 𝐭𝐨 𝐡𝐞𝐥𝐩 𝐲𝐨𝐮 𝐜𝐫𝐚𝐜𝐤 𝐭𝐡𝐞 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰𝐬 𝐨𝐟 𝐭𝐨𝐩 𝐩𝐫𝐨𝐝𝐮𝐜𝐭 𝐛𝐚𝐬𝐞𝐝 𝐜𝐨𝐦𝐩𝐚𝐧𝐢𝐞𝐬 𝐛𝐲 𝐝𝐞𝐯𝐞𝐥𝐨𝐩𝐢𝐧𝐠 𝐚 𝐭𝐡𝐨𝐮𝐠𝐡𝐭 𝐩𝐫𝐨𝐜𝐞𝐬𝐬 𝐚𝐧𝐝 𝐚𝐧 𝐚𝐩𝐩𝐫𝐨𝐚𝐜𝐡 𝐭𝐨 𝐬𝐨𝐥𝐯𝐞 𝐚𝐧 𝐮𝐧𝐬𝐞𝐞𝐧 𝐏𝐫𝐨𝐛𝐥𝐞𝐦."
𝐇𝐞𝐫𝐞 𝐢𝐬 𝐡𝐨𝐰 𝐲𝐨𝐮 𝐜𝐚𝐧 𝐫𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐟𝐨𝐫 𝐭𝐡𝐞 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 -
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLINR
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐨𝐮𝐭𝐬𝐢𝐝𝐞 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLUSD
𝐖𝐚𝐧𝐭 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐁𝐢𝐠 𝐃𝐚𝐭𝐚 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫?
𝐜𝐡𝐞𝐜𝐤𝐨𝐮𝐭 𝐭𝐡𝐞 𝐛𝐢𝐠 𝐝𝐚𝐭𝐚 𝐜𝐨𝐮𝐫𝐬𝐞 𝐝𝐞𝐭𝐚𝐢𝐥𝐬
𝐖𝐞𝐛𝐬𝐢𝐭𝐞 : trendytech.in/?src=youtube&su...
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 :)
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
🔅Sumit LinkedIn - / bigdatabysumit
🔅Sumit Instagram - / bigdatabysumit
#SQL #RDBMS #database #bigdata #dataengineering

Пікірлер: 47

@sumitmittal07 Жыл бұрын

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

@hafmouss584 Жыл бұрын

Link does work

@Nikhil-qi4oz 2 ай бұрын

Please continue SQLseries

@pridename2858 10 ай бұрын

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

@sudarshanmhaisdhune1039 Жыл бұрын

Simple, smooth & very much practical example & tutorial.

@GauravSharmagvs 2 жыл бұрын

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

@vikasgadhe7620 2 жыл бұрын

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

@hafmouss584 Жыл бұрын

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

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

@Sahel219 Жыл бұрын

Very helpful. Thank you!

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

@krushnabelokar5783 Жыл бұрын

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

@slyroy7562 2 жыл бұрын

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

@tejasbavkar2811 Жыл бұрын

Thank you sir👍

@muthu_vrn 3 ай бұрын

Fantastic

@user-hd8of6mm3z 6 ай бұрын

you nailed it

@brijesh0808 Жыл бұрын

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

@ningus8522 Жыл бұрын

thank u sir...

@itsall5180 4 ай бұрын

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

@arvindkumarsingh8476 Жыл бұрын

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

@anuradhadhere4306 Жыл бұрын

Pls make videos on PLSQL

@kryurr 8 ай бұрын

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

@Akashsingh-re9dk 2 жыл бұрын

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;

@udayptp 2 жыл бұрын

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

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

@rakhikumari190 Жыл бұрын

please cover 177 no leetcode question

@TrueRDX Жыл бұрын

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

@veereshk6065 2 жыл бұрын

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 ?

@swadhinsonwane4185 Жыл бұрын

Sir please explain the temptable concept

@user-lh4di9zg3u Жыл бұрын

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

@varuntirupati2566 2 жыл бұрын

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

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

@ShivamSharma-ou1mz 2 жыл бұрын

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

@sumitmittal07 2 жыл бұрын

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

@user-lh4di9zg3u Жыл бұрын

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

@sc4647 2 жыл бұрын

RIP Shane warne

@abhaysagar935 Жыл бұрын

Samajh nhi aa raha

@balramlowanshi3819 2 жыл бұрын

Pls send notes Sir

@sumitmittal07 2 жыл бұрын

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

@balajibalasubramaniam8349 2 жыл бұрын

plsql

@yashdhas8202 2 жыл бұрын

please take regular expressions

@sumitmittal07 2 жыл бұрын

Yes definitely :)

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