How to Get 4th Largest salary without using "join" or "where" in SQL ?

[santoshsri]

Hello,

Is there any by which I can write a SQL to get the 4th largest salary from a table without using "join" and without using "where" clause.

Suppose the table is having two columns : Employee_id, Employee_salary.

Please let me know..

I faced this question in an interview and I did not have any answer for this.

Regards,
San

[mcasaurabhsumit]

Hi,
you will have to use either join or where for getting forth highest salary.

use nested query

select max(salary) from emp where salary<(select max(salary) from emp where salary<(select max(salary) from emp where salary<(select max(salary) from emp)))

use corrlated query

select * from emp e where 3=(select count(distinct sal) from emp x where x.sal>e.sal)

[Phil Curtis]

Hello,

Is there any by which I can write a SQL to get the 4th largest salary from a table without using "join" and without using "where" clause.

Suppose the table is having two columns : Employee_id, Employee_salary.

Please let me know..

I faced this question in an interview and I did not have any answer for this.

Regards,
San

Assuming that you have 4 or more rows in the table:

Select Top 4 * from Emp order by Employee_salary desc

The 4th row is the one that you want.