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.

What is a relationship and what are they?

0 Answers  

How do I view stored procedures?

0 Answers  

What is crud diagram?

0 Answers  

What is the difference between rename and alias?

0 Answers  

How can you maintain the integrity of your database on instances where deleting an element in a table result in the deletion of the element(s) within another table?

0 Answers  

What is sqlerrd?

0 Answers  

What does cursor do in sql?

0 Answers  

What is rollback?

0 Answers  

When do we create bitmap indexes

2 Answers   CTS,

what is msql? : Sql dba

0 Answers  

find the third highest salary?

23 Answers  

what is difference between procedure and function, procedure and trigger?

8 Answers   iFlex,