how to calcuate the second highest salary of he employee
Answers were Sorted based on User's Feedback
Answer / neetika vardhan
SELECT MAX(sal)
FROM EMP WHERE sal NOT IN (SELECT MAX(sal) FROM emp)
Is This Answer Correct ? | 41 Yes | 3 No |
Answer / ramya p
SELECT DISTINCT (sal),deptno FROM emp a
WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM emp b
WHERE a.sal<=b.sal)
N is the Nth highest salary you want to print. In this case
N=2
Is This Answer Correct ? | 7 Yes | 1 No |
Answer / hari krishna j
SELECT A.SAL FROM EMP_TAB A
WHERE 1=(SELECT COUNT(*) FROM EMP_TAB B
WHERE B.SAL > A.SAL)
Change value of 1 if
0 - First heighest sal
1 - Second " "
2 - Third heighest sal
.
.
.
Is This Answer Correct ? | 5 Yes | 0 No |
Answer / udhayakumar
select salary from (select salary from emp order by salary
desc) where rownum=2;
Is This Answer Correct ? | 3 Yes | 2 No |
Answer / eshwer
Try this
select * from (SELECT empno,sal, DENSE_RANK() OVER (ORDER BY sal desc) AS SalRnk
FROM scott.emp) where salrnk=2;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / vinayak jamdar
Select top 1 empid,salary from (select distinct top 2
empid,salary from emp order by salary desc)a order by salary
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / sunilpanghal
select rownum as rowid,salary from (salary from employees
oderby salary )where rownum <2
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / sachin s. patil
SELECT * FROM
(SELECT ROWNUM DD, M.* FROM
(SELECT SAL FROM EMP order by sal desc )M)
WHERE DD=2
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / lalit
select name, sal from (select name,sal from emp where sal<(select max(sal) from emp) order by sal desc)where rownum=1;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / sachin s. patil
SELECT SAL FROM
(SELECT ROWNUM DD, M.* FROM
(SELECT SAL FROM EMP order by sal desc )M)
WHERE DD=2/***HERE U CAN ADD ANY NUMBER (WHERE I PUT 2)
, U WILL GET HEIGHEST SALARY THAT POSITION *******/
Is This Answer Correct ? | 0 Yes | 1 No |
Cite the differences between execution of triggers and stored procedures?
What happens when a trigger is associated to a view?
What is sql profiling in oracle?
How do you update a sql procedure?
How to create an array in pl/sql?
define sql delete statement ? : Sql dba
What is a database? Explain
what is the difference between delete and truncate commands? : Sql dba
What are the blocks in stored procedure?
How do I filter in sql profiler?
What is a trigger in pl/sql?
1.when will you use week refcursor and when will you use strong ref cursor ? 2.what is the use of sql trace..how do you use it ? 3.can you please send all the sql plus commands...like set line 6000....