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 ? | 42 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 |
What is the usage of distinct keyword?
Under what condition it is possible to have a page level lock and row lock at the same time for a query? : Transact sql
explain the difference between delete , truncate and drop commands? : Sql dba
How can you view the errors encountered in a trigger?
What are the different types of sql commands?
Is sqlite free?
What is cross join example?
How to change a value of the field ‘salary’ as 7500 for an employee_name ‘john’ in a table employee_details?
Is nosql faster than sql?
what is explain plan?, given an example...
How do you explain an index?
I i have 2 tables A & B ,A contains 10 records B contains 20 records ,what w'll be the o/p if we perform equijoin,outer join,right outer join,left outer join,full outer join,cross join seperately Can anyone help on this?