SQL Faqs

find the two minimum salaries among table
This Query returns duplicate value.
select sal from (select * from order by sal asc) where rownum < 3

This Query retuns only the second minimum salary
Select min(salary) from where Salary>(Select min(salary) from )

Whereas the following Query returns the distinct first two minmum salary in a table
Select min(salary) from union
Select min(salary) from where salary>(Select min(salary) from )

Find out nth highest salary from emp table
directly we can not give the condition as rownum=2....this is the most optimal soluton for the given problem
select * from (select rownum r,salary from (select distinct(salary)from employees where salary is NOT NULL order by salary desc)) where r=2
Best way is using CORRELATED SUBQUERY : as below
Select * From TableName T1 Where (N-1) = (Select Count(Distinct(E2.ColumnName)) From TableName T2 Where T2.ColumnName > T1.ColumnName)
For Required Case We can use it as :-
Select * From EMP T1 Where (2-1) = (Select Count(Distinct(E2.sal)) From EMP T2 Where T2.sal > T1.sal)