how to get second highest salary in SQL(as/4000
Answers were Sorted based on User's Feedback
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....
Is This Answer Correct ? | 2 Yes | 0 No |
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
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mohamed shahid
select * from (select sal,rownum rn from(select sal from emp order by sal desc)) where rn=2;
Is This Answer Correct ? | 1 Yes | 0 No |
select max(salary) from table
where salary < (select max(salary) from table);
Is This Answer Correct ? | 2 Yes | 2 No |
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
Is This Answer Correct ? | 0 Yes | 0 No |
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
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / sachin
select max(salary) from emp_salary where salary<(select
max(salary) from emp_salary);
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / chandu
select max(sal) from new where sal not in(select max(sal)
from new);
Is This Answer Correct ? | 0 Yes | 0 No |
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
Is This Answer Correct ? | 4 Yes | 5 No |
Answer / praveen agrawal
select sal from employee order by sal desc limit 1,1;
its 100% true i have checked it out.
Is This Answer Correct ? | 1 Yes | 2 No |
What is the use of prepared statement?
Does it possible to pass object (or) table as an argument to a remote procedure?
What is sql profiler in oracle?
What is the purpose of the partition table?
what is meant by urlencode and urldocode? : Sql dba
how to rename an existing table in mysql? : Sql dba
How to update salary of employees department wise?
What is sap sql?
how to create a new view in mysql? : Sql dba
When is a declare statement required?
How to avoid using cursors?
How to get the 3rd column(i.e all the data along with the column name)in a table?