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.

Explain the difference between triggers and constraints?

0 Answers  

How do I edit a trigger in sql developer?

0 Answers  

How exception handling is done in advance pl/sql?

0 Answers  

Is a secondary key the same as a foreign key?

0 Answers  

How can you create Cursor with parametres ?

3 Answers  

How can we overcome recursive triggers in SQL?

1 Answers   iFlex,

What is cte?

0 Answers  

How do you update a value in sql?

0 Answers  

What is constant in pl sql?

0 Answers  

How do you define a foreign key?

0 Answers  

query to retrive the employees whose sal is greater than avg sal

10 Answers   TCS,

How does pl sql work?

0 Answers