Interview Question | How to find 2nd max salary in a table using analytical functions in oracle

This video is useful to find the nth max salary from a table. It shows all the different ways and possibilities to find the nth max salary from a table, using a rownum sub query and the later on using a dense rank function.

[LoveIndia]

Thanks a lot Sir for this Video...easy to understand .. i visited ample number of videos for highest salary..and then came to this one.. i found this one is best of all...this the best explanation for highest salary...

[naveenraj]

thanks bro. i understood in first attempt itself . thank you

[whynotblueapples]

Kishan your video is absolutely clear to explain and understand this question! You have a natural talent to explain.
Now I want to contribute with constructive criticism in case you want to improve the presentation of your videos, is simples but gives you a little bit more work.
So.. you can buy a mic to record your voice, so it will be much more clear. Then you can record your screen and sound separately, and after join both on a final product a bit more polished.

All in all your content it's absolutely useful! Many thanks

[rajanburad]

Hi Sir, very well explained. Please share some more videos which contains some scenarios and uses subqueries.
Also, at 2.49 you averred that this query works only when we have distinct values but we can also do it using group by function, eg: -
select * from (select salary, rownum as Rank from (select salary
from employees
where salary is not null
group by salary
order by salary desc))
where Rank =2;
This also returns distinct values.
Please correct me if I am wrong.

[satvinderbhangu]

Hi do you have any video related to Clob and blob and file related how we can right file and load apeand file

[subhits]

Use this query: - SELECT salary FROM employee_salary
ORDER BY salary DESC
LIMIT 1 OFFSET n;
to get nth highest salary.

[shruthishetty]

Kishan please upload more videos on plsql. These videos was very helpful for me to face the interview .And one more help do u have any reference videos for teradata ?

[TheDreamer]

Hi Kishan,
I have a doubt when you used the rank function, doesn't it give the same rank to duplicate values such as the salary '17000'? Is the rank different here because of the rownum as rank operator?

[sameerahmad]

Hi Kishan, in need of your help....while installing Oracle 11g Enterprise edition my Quick heal antivirus is telling about any Ransomware detection. Due to which I am unable to install enterprise edition. Whereas I am able to install 11g Express Edition.
Kindly tell me the solution to

[HariharanSuresh]

Isn't this a better solution ?
SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)


Granted the performance is low, but I think this can be used on many DB's and not just Oracle DB

[sheaqbaazi]

I have two tables called as employee, department how to find max salary of each department

[yogeshmore]

SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY SAL DESC) AS RK, E.* FROM EMP E) WHERE RK =3;