How to retrieve a second highest salary from a table?
Note:Suppose salaries are in duplicate values
eg:
Name Sal
Malli 60000
Pandi 60000
Rudra 45000
Ravi 45000
Answers were Sorted based on User's Feedback
Answer / mallinathabj
The Following queries to retrieve the second highest salary
SQL> select max(sal) from emp where sal<(select
max(distinct(sal)) from emp);
SQL> select min(sal) from(select distinct( sal) from emp order
by sal desc) where rownum<=2;
SQL> SELECT MAX(SAL) FROM EMP WHERE SAL NOT IN (SELECT
MAX(SAL) FROM EMP);
Is This Answer Correct ? | 13 Yes | 2 No |
Answer / bibu
This IS HELP FULL 1ST ,2ND,3RD,4TH ANY HIGHEST SALARY U WRITE THIS QUERY.
SELECT * FROM Table_Name ALIAS_NAME 1
WHERE &N=(
SELECT COUNT(DISTINCT(WHICH COL U WANT THAT COL_NAME))
FROM Table_Name ALIAS_NAME 2
WHERE ALIAS1.CoL<=ALIAS2.Col
)
EX:
SELECT * FROM Emp E1
WHERE &N=(SELECT COUNT(DISTINCT(Sal))
FROM Emp E2
WHERE E1.Sal<=E2.Sal)
Is This Answer Correct ? | 11 Yes | 0 No |
Answer / prativa mishra
select *
from
(select salary,dense_rank() over (order by salary) kk from table_name)
where kk=2
Is This Answer Correct ? | 6 Yes | 2 No |
Answer / praveenpinfo
Suppose table name is EMP.
SQL> Select distinct sal from emp e1 where 2=(select
count(distinct sal) from emp e2 where e1.sal<=e2.sal);
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / ashok
select sal from emp order by sal desc offset 1 rows fetch next 1 rows with ties;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / dewesh
With cte as (
Select *, Dense_rank() over (order by salary desc) as s from emp)
Select EmpName, Salary from cte where s =2
It has work. But duplicate value is not removed if name is different
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / welcomeashwin
--2 ND HIGHEST SALARY
SELECT MAX(SALARY) FROM HR.EMPLOYEES
WHERE SALARY < (SELECT MAX(DISTINCT(SALARY)) FROM
HR.EMPLOYEES);
--N TH HIGHEST SALARY
SELECT * FROM HR.EMPLOYEES EMP1
WHERE &N=(
SELECT COUNT(DISTINCT(SALARY))
FROM HR.EMPLOYEES EMP2
WHERE EMP1.SALARY<=EMP2.SALARY
);
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / sreeharibabu
SELECT id
FROM (select salary2.*, rownum rnum from
(select * from test ORDER BY id DESC) salary2
where rownum <=2 )
WHERE rnum >= 2;
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / rajat
SELECT * FROM (
SELECT RANK()OVER( ORDER BY SALARY DESC) NUM, A.* FROM RAJ A)
WHERE NUM=2;
Is This Answer Correct ? | 0 Yes | 1 No |
Mention what are different methods to trace the pl/sql code?
How do you change a value in sql?
Explain autonomous transaction.
What is Highwatermark?
What is meant by Join? What are the different types of Joins available? Explain.
what does myisamchk do? : Sql dba
how to show all tables with 'mysql'? : Sql dba
how to create a database in oracle?please gve anser with example
What is oracle sql called?
How to load data with sql*loader?
What is dml and ddl?
What is rank () in sql?