Answer / santosh kumar

select employee_id,last_name,salary
from
(select rownum r,employee_id,last_name,salary
from (select employee_id,last_name,salary from employees
order by salary desc)) a
where a.r=&nth_salary;

---u can find nth highest salary----
for more detail u can contact me at
rajgupta7666@gmail.com....

Answer / selvaspak

select sal from (select sal,rank() over(order by sal desc)
as rank from emp) where rank = &no

Actually for this Question there are having lot of method
to get the result, but the interviewer is asking this for
to check whether the interview person know the RANK()
function or not. So the above query is correct

Answer / mohamed shahid

select * from (select sal,rownum rn from(select sal from emp order by sal desc)) where rn=2;

Answer / dharmendra

select max(salary) from table
where salary < (select max(salary) from table);

Answer / tauseef

select last_name,salary from(
select last_name,salary ,rownum rk from(
select distinct salary,last_name from employees order by
salary desc nulls last))
where rk=9


By this way u can find 2nd,3rd n nth highest salary wit the
name

Answer / amedela chandra sekhar

SQL> select * from emp where sal=(select max(sal) from emp
2 where sal<(select max(sal)from emp));

EMPNO ENAME JOB MGR HIREDATE
SAL COMM
---------- ---------- --------- ---------- ---------
---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20

7902 FORD ANALYST 7566 03-DEC-81 3000
20

Answer / sachin

select max(salary) from emp_salary where salary<(select
max(salary) from emp_salary);

Answer / chandu

select max(sal) from new where sal not in(select max(sal)
from new);

Answer / s. syam sundar

1.SELECT LEVEL,MAX(SAL) FROM EMP WHERE LEVEL = 2 CONNECT BY
PRIOR SAL >SAL GROUP BY LEVEL
2.SELECT SAL FROM (SELECT SAL FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <3
MINUS
SELECT SAL FROM (SELECT SAL FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM = 1
3.SELECT MAX(SAL) FROM EMP WHERE SAL <> (SELECT MAX(SAL)
FROM EMP)

regards
syam sundaar

Answer / praveen agrawal

select sal from employee order by sal desc limit 1,1;

its 100% true i have checked it out.

Is left join inner or outer?

0 Answers  

What is a sql*loader control file?

0 Answers  

Which data dictionary views have the information on the triggers that are available in the database?

0 Answers  

How do I order columns in sql?

0 Answers  

Is null operator in sql?

0 Answers  

How the execution will be done in exceptions?

2 Answers  

How do I save a sql query?

0 Answers  

What is sql character function?

0 Answers  

what is the different between now() and current_date()? : Sql dba

0 Answers  

Explain what is sql?

0 Answers  

What normalization means?

0 Answers  

how can you create an empty table from an existing table? : Sql dba

0 Answers