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.

Does normalization improve performance?

0 Answers  

How do you take the union of two tables in sql?

0 Answers  

if table named a is there and 4 records are there then how to swap (1 and 3) and (2 and 4) records at a time

1 Answers  

How to process query result in pl/sql?

0 Answers  

What is sql dialect?

0 Answers  

what is foreign key? : Sql dba

0 Answers  

What is difference between table and view?

0 Answers  

Are stored procedures faster than queries?

0 Answers  

If I have a table T with 4 rows & 2 columns A & B. A has values 1,2,3,4. and B has 10,20,30,40. Write an Update SQL query which can Swap the values of A & B for all records. (Do not use a sub-query)

5 Answers  

How does a covering index work?

0 Answers  

Is sql a dbms?

0 Answers  

Is nosql faster than sql?

0 Answers