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.

i have one table with accounts and amounts as colums.accounts with columns c and d. and amounts with values 1000,2000,3000 for c and 4000,5000,8000 for d.i need to find the sum of these accounts c and d individually and find the differences between their sum using one select statement.

11 Answers   Ebix, Hewitt,

how to write date and time literals? : Sql dba

0 Answers  

How do I turn a list into a table?

0 Answers  

What is compilation error in pl sql?

0 Answers  

How collections are improve the performance?

1 Answers   Polaris,

How do I send sql query results to excel?

0 Answers  

What is a variable in sql?

0 Answers  

How many null values can be inserted in a coulmn whihc is unique constraint

8 Answers   Flextronics,

What is compiled query?

0 Answers  

What are the different schemas objects that can be created using pl/sql?

0 Answers  

how to drop an existing table in mysql? : Sql dba

0 Answers  

Is mariadb nosql?

0 Answers