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🙏🏻🙏🏻🙏🏻
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.
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...
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;
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 .. :)
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??
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
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?
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
Hi, if you want to display all employees who share the highest salary in a city, you should replace ROW_NUMBER() with RANK() or DENSE_RANK() as explained in the next video. SELECT * FROM ( SELECT firstname, lastname, salary, location, RANK() OVER (PARTITION BY location ORDER BY salary DESC) as salary_rank FROM employee ) t1 WHERE t1.salary_rank = 1;
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
Hi, if you want to display all employees who share the highest salary in a city, you should replace ROW_NUMBER() with RANK() or DENSE_RANK() as explained in the next video. SELECT * FROM ( SELECT firstname, lastname, salary, location, RANK() OVER (PARTITION BY location ORDER BY salary DESC) as salary_rank FROM employee ) t1 WHERE t1.salary_rank = 1;
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 ?
Checkout the Big Data course details here: trendytech.in/?referrer=youtube_sql15
Link does work
Please continue SQLseries
Excellent learning...getting so easy to understand and learning of SQL's most clever tricks....Thank you sir
Simple, smooth & very much practical example & tutorial.
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🙏🏻🙏🏻🙏🏻
Thank you so much Sir, you explain better than many paid training and school
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.
But this doesn't show rownumber 5
It's gives only 5th highest salary
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...
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;
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 .. :)
thanks for such a wonderful tutorial.
I have been following your content for a long time.
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');
As always Excellent tutorial. Thanks a lot for sharing this video.
you nailed it
Very helpful. Thank you!
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??
we will learn Rank and Dense Rank in upcoming sessions. your question will be answered there.
Thank you sir👍
Fantastic
Thank Q sir..
If it is possible can you make one video related to Date functions and some scenarios
Very nice and cristal clear explanation sir of the row function appreciate your work. Congratulations you got one subscriber 🎉 keep it up sir 👍
thank u
sir...
@3:30 Sir, for me it is working even without sepcifying order by
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
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?
order by is mandatory whereas partition by optional for all the windowing function
Pls make videos on PLSQL
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
Hi, if you want to display all employees who share the highest salary in a city, you should replace ROW_NUMBER() with RANK() or DENSE_RANK() as explained in the next video.
SELECT * FROM (
SELECT firstname, lastname, salary, location,
RANK() OVER (PARTITION BY location ORDER BY salary DESC) as salary_rank
FROM employee
) t1
WHERE t1.salary_rank = 1;
Sir please explain the temptable concept
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
Incase of same salary in few rows, the row_number approach will fail. Right ?
yes, just replace it with rank() or dense_rank()
More correct way will be using Dense Rank function instead of Row Number for calculating Nth Salary
Yes.. that we will cover in next section on how to optimize this solution
Yes, with Dense_rank we will get all the employess whose having same salary
please cover 177 no leetcode question
sir please also give use the table so dont need to write the query;
why for bangalore 1 record, it should show 2 records as bangalore holds two 1st rank right ??
Hi, if you want to display all employees who share the highest salary in a city, you should replace ROW_NUMBER() with RANK() or DENSE_RANK() as explained in the next video.
SELECT * FROM (
SELECT firstname, lastname, salary, location,
RANK() OVER (PARTITION BY location ORDER BY salary DESC) as salary_rank
FROM employee
) t1
WHERE t1.salary_rank = 1;
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 ?
Pls send notes Sir
Will try adding the link to it in the description section.
please take regular expressions
Yes definitely :)
Samajh nhi aa raha
plsql
I got confusing 😭😭