Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022. The Coursera Plus membership gets you access to unlimited courses and unlimited certifications! imp.i384100.net/Ke51on
Have you been asked a SQL query interview question that you couldn't answer? Let us know in the comments below and we will answer those in our upcoming videos!
In your second way if you have 3 employees with same salary then we will not be able to get nth highest salary. Lets say we have 3 employees with salary 85000 and 4th is 70000 then it means our 2nd highest salary is 70000 not 85000.
Make your inner query "SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC" and you'll get unique salaries. But both would be correct, so you should ask your interviewer to clarify what he means. :)
in this era, efficient coding matters and we could directly get nth salary by rank/ rownumber(), but told us all the jugad we can do to get nth salary...... amazing....... thanks.....
I mean, in Oracle, you'd just do something like this: SELECT * FROM EMPLOYEE ORDER BY salary desc OFFSET (n-1) ROWS FETCH FIRST 1 ROW ONLY ; And MS SQL Server has an offset or skip or something too. I'm sure of it.
@@andresilvasophisma And? It's a one off query. You could, I guess select, order by salary fetch first n only, and select from that again, but order asc fetch first 1 row only. But then we're assuming that column is indexed, and that is not a safe assumption either...
i'm using postgresql and there are couple of solution for this select max(salary) from employees where salary < (select max(salary) from employees) ----------------------------------------------- select * from employees where salary < (select max(salary) from employees) order by salary desc limit 1; or limit 3 for top 3 salaries ------------------------------------------------------------- finding top 3 salaries select * from (select e.*, dense_rank() over(order by salary desc) as top_3_sal /we can use row_number/rank() based on the data and scenario from employees e) where top_3_sal
Thanks for sharing the video's with us. I like the way which you have explained. These are the basic question which we face in interviews. Could you please share the video link if you have explained about analytical functions ?
Thank You. We have started including the datasets in our recent video tutorials. A similar dataset as needed for this example is provided in the tutorial on SQL Complex Queries which can be accessed here - th-cam.com/video/E_6haE_10eE/w-d-xo.html
Thanks for posting relevant content. Can you please help me with below scenario How do we group or list first 10 rows combine into a another single row and then another 10 rows combine into a another row, this should go on till the end?
You can use Windows function as mentioned in the post below. stackoverflow.com/questions/41656620/sql-query-for-calculating-average-in-every-n-rows-with-step-1
Hi ma'am. Thanks for this tutorial. I am using postgres so can I use OFFSET and LIMIT together to fetch Nth record? For example, to fetch the 3rd highest salary, can we do something like this? SELECT salary FROM employee ORDER BY salary DESC OFFSET 2 LIMIT 1;
Yes, but not in case if first two or three highest salary are the same. Dense_rank function does slightly different thing than offset/limit. Dense rank will return all emps with 3rd highest salary regardless of count of employees with 1st and 2nd highest salary.
Madam, if there are duplicate Salaries then top will not work. For example : If there are salaries like 5000, 4000, 3000, 3000 etc. and we want top 3rd highest salary. If we use top 3 means it will take 5000, 4000, 3000, it wont take another 3000
If we use the Order by clause, then we use could Top 3 with Ties. This will return all rows whose values tie with the record in the 3rd position. We could always create a list of distinct(unique) salaries first and then return the top x salaries and wouldn't have to worry about duplicates.
In T-SQL, the dialect used by Microsoft, there is no Nth function. The easiest way to find the Nth highest or lowest value in dataset is by using the Row_Number() function. It's a Window function and makes use of the Over clause. Actually, the Rank() and Dense_Rank() functions could as be used as well but Row_Number() is a fine choice. The only real difference between the three functions has to do with tied row values. Row_Number() ignores tied row values. The other two do not. If there are no tied row values in the dataset , then all three functions behave exactly the same. Once you work out your logic you could create either a user defined scalar function, or a user define table valued function to make it easy to invoke when needed.
You need to decide between using rank, dense rank and row number. They number the rows in a slightly different way. You can learn more about the functions here - docs.microsoft.com/en-us/answers/questions/211223/what-is-the-difference-among-row-number-rank-and-d.html
@@LearnatKnowstar the power of sql is in its expressiveness. The solution provided by a pal from upstairs is much clearer and is more understood. By the way the solution uses only sql syntax that can be ported into any dbms scripts without almost any change
Unfortunately, the database for this tutorial is not available but a similar database is available for the tutorial on SQL Complex queries which can be accessed here - th-cam.com/video/E_6haE_10eE/w-d-xo.html
Why is this written as an "interview question". It is merely a tip that helps people improve their skills. The obsessive focus of predominantly Indian nationals on "Passing exams" & "passing interviews" can disadvantage their fellow countryman. They get perceived as just capable of scraping thru an interview. Only to find they are useless when actually doing any task that requires them to assimilate a variety of techniques & coding patterns to solve real business problems. As this has nothing to do with interviews. Why phrase it that way?
Thank you for sharing your thoughts. You are right in saying that greater learning is needed and passing an interview should not be an end in itself. This should be true for everyone and not any one particular country. We have all encountered brilliant coders from all countries including India. Cheers for that !
Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!
imp.i384100.net/Ke51on
Have you been asked a SQL query interview question that you couldn't answer?
Let us know in the comments below and we will answer those in our upcoming videos!
In your second way if you have 3 employees with same salary then we will not be able to get nth highest salary. Lets say we have 3 employees with salary 85000 and 4th is 70000 then it means our 2nd highest salary is 70000 not 85000.
Make your inner query "SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC" and you'll get unique salaries.
But both would be correct, so you should ask your interviewer to clarify what he means. :)
You are explaining in precise manner that every one can understand who does not even know about SQL.
Thank you
i would use row_number over (partition by EmployeeIr order by Salary desc) as rowc. And then use it as a subquery and use "where rowc = nth position"
in this era, efficient coding matters and we could directly get nth salary by rank/ rownumber(), but told us all the jugad we can do to get nth salary...... amazing....... thanks.....
thanks a lot .. I downloaded all series to learn me all these beautiful triks and samples ..thanks again
Thank you for your support 👍
can use row_number over(order by salary desc) as rnk , put it in nested select and then select where rnk = 9
VERY INFORMATIVE AND UNDERSTANDABLE.
Thank you
I mean, in Oracle, you'd just do something like this:
SELECT *
FROM EMPLOYEE
ORDER BY salary desc
OFFSET (n-1) ROWS FETCH FIRST 1 ROW ONLY
;
And MS SQL Server has an offset or skip or something too. I'm sure of it.
Offset is not a good solution as it has to process all of the offset records so that it can get to the one that you want.
@@andresilvasophisma
And? It's a one off query.
You could, I guess select, order by salary fetch first n only, and select from that again, but order asc fetch first 1 row only.
But then we're assuming that column is indexed, and that is not a safe assumption either...
You are a great teacher! Thank you very much
Thanks so much!
Thanks for sharing 👍
Thank you
i'm using postgresql and there are couple of solution for this
select max(salary) from
employees
where salary < (select max(salary) from employees)
-----------------------------------------------
select * from employees
where salary < (select max(salary) from employees)
order by salary desc limit 1; or limit 3 for top 3 salaries
-------------------------------------------------------------
finding top 3 salaries
select * from (select e.*,
dense_rank() over(order by salary desc) as top_3_sal /we can use row_number/rank() based on the data and scenario
from employees e)
where top_3_sal
Thanks for sharing the video's with us. I like the way which you have explained. These are the basic question which we face in interviews. Could you please share the video link if you have explained about analytical functions ?
Thank you
You might find the below playlist useful
th-cam.com/play/PL2-GO-f-XvjBl5fpzdfYaPW28PwsLzLc4.html
This video explains the rank, row number and dense rank functions
th-cam.com/video/oBrHabyDCTk/w-d-xo.html
@@LearnatKnowstar Thanks a lot. This would be helpful for me
Ima download it thanks for sharing!!
I have been asked this a lot of times. Thanks for posting relevant content.
Thank you
The rank function does a better job than the multiple nested querries
explanation is awesom if you provide dataset then it will more helpfull .because it will take much to create again and again different table.
Thank You. We have started including the datasets in our recent video tutorials. A similar dataset as needed for this example is provided in the tutorial on SQL Complex Queries which can be accessed here -
th-cam.com/video/E_6haE_10eE/w-d-xo.html
@@LearnatKnowstar Thanks 🙂
Could you please share the link for the Demo Database you are using in this video.Thanks
Thanks for posting relevant content.
Can you please help me with below scenario
How do we group or list first 10 rows combine into a another single row and then another 10 rows combine into a another row, this should go on till the end?
You can use Windows function as mentioned in the post below.
stackoverflow.com/questions/41656620/sql-query-for-calculating-average-in-every-n-rows-with-step-1
We will do a detailed video soon. Thank you for sharing your query.
Thank you!
Hi ma'am. Thanks for this tutorial.
I am using postgres so can I use OFFSET and LIMIT together to fetch Nth record?
For example, to fetch the 3rd highest salary, can we do something like this?
SELECT salary
FROM employee
ORDER BY salary DESC
OFFSET 2 LIMIT 1;
Should be working
Yes, but not in case if first two or three highest salary are the same. Dense_rank function does slightly different thing than offset/limit. Dense rank will return all emps with 3rd highest salary regardless of count of employees with 1st and 2nd highest salary.
Thanks u for sharing 😘
Thank you
Very good content. As a feedback, if you could zoom in the screen to show the code it would be better to watch your videos on smartphones
Thank you. In more recent videos, you can find bigger font size👍
Select * from emp e1 where n=(select count(distinct e2.rownu.)
From emp e2 where e1.sal
You are a bless in my life 😘
Rank=2
Madam, if there are duplicate Salaries then top will not work.
For example :
If there are salaries like 5000, 4000, 3000, 3000 etc. and we want top 3rd highest salary.
If we use top 3 means it will take 5000, 4000, 3000, it wont take another 3000
If we use the Order by clause, then we use could Top 3 with Ties. This will return all rows whose values tie with the record in the 3rd position.
We could always create a list of distinct(unique) salaries first and then return the top x salaries and wouldn't have to worry about duplicates.
Excellent video. In the first option of nth max salary i f we want 3rd max salary do we need to write subqueries 3 times?
have you got solution using max function if so mention the code
@@tarungangadhar14 i didn't try yet
Well, can't we just use LIMIT keyword
Select * from (Select * from Table_Name order by salary desc limit n) as v order by salary limit 1
Yes, sure. This will work as well.
Thanks, isn't there something like a" nth_ " function?
In T-SQL, the dialect used by Microsoft, there is no Nth function. The easiest way to find the Nth highest or lowest value in dataset is by using the Row_Number() function. It's a Window function and makes use of the Over clause. Actually, the Rank() and Dense_Rank() functions could as be used as well but Row_Number() is a fine choice. The only real difference between the three functions has to do with tied row values. Row_Number() ignores tied row values. The other two do not. If there are no tied row values in the dataset , then all three functions behave exactly the same.
Once you work out your logic you could create either a user defined scalar function, or a user define table valued function to make it easy to invoke when needed.
If there is a tie in salary how will we get the nth highest as values will be duplicated.how to solve this issue.
You need to decide between using rank, dense rank and row number. They number the rows in a slightly different way.
You can learn more about the functions here -
docs.microsoft.com/en-us/answers/questions/211223/what-is-the-difference-among-row-number-rank-and-d.html
mam if you will give create and insert script, it would be very helpful
what about if you have max salary having more then one employee by using top clause it shows only one employee details
Thank u sur
How to find 3rd. High salary by using CTE ?
Dude, why not just use "order by salary" then "offset" the number of row you want to skip then "fetch " the first row
That’s exactly what I was thinking.
You can certainly do the offset method as well. Thanks for mentioning it.
@@LearnatKnowstar the power of sql is in its expressiveness. The solution provided by a pal from upstairs is much clearer and is more understood. By the way the solution uses only sql syntax that can be ported into any dbms scripts without almost any change
Presentation is not clear, if picture is clear this will be very helpful
Please try changing the quality to HD.
SELECT E_NAME, E_SAL, ROW_NUMBER() OVER ( ORDER BY E_SAL DESC) AS POSITION FROM EMPS ORDER BY E_SAL DESC
THIS WAY ALSO WE CAN
Where is the database file for this
Unfortunately, the database for this tutorial is not available but a similar database is available for the tutorial on SQL Complex queries which can be accessed here -
th-cam.com/video/E_6haE_10eE/w-d-xo.html
Rownum is best to find nth sal
Why is this written as an "interview question". It is merely a tip that helps people improve their skills. The obsessive focus of predominantly Indian nationals on "Passing exams" & "passing interviews" can disadvantage their fellow countryman. They get perceived as just capable of scraping thru an interview. Only to find they are useless when actually doing any task that requires them to assimilate a variety of techniques & coding patterns to solve real business problems.
As this has nothing to do with interviews. Why phrase it that way?
Thank you for sharing your thoughts. You are right in saying that greater learning is needed and passing an interview should not be an end in itself. This should be true for everyone and not any one particular country. We have all encountered brilliant coders from all countries including India. Cheers for that !
hi can u please tell where is the question code and solution code of query u used in the video it will be great help