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 / 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 / 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 ? | 12 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 / prativa mishra
select *
from
(select salary,dense_rank() over (order by salary) kk from table_name)
where kk=2
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / ashok
select sal from emp order by sal desc offset 1 rows fetch next 1 rows with ties;
Is This Answer Correct ? | 0 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 |
What are nested triggers ?
6 Answers Amazon, Appeal Soft, IBM, Infosys, TCS,
Can a table have two primary keys?
Create a procedure to delete certain records from a table and display the total number of records deleted in this process. (Condition for deletion can be of ur choice, for instance delete all records where eid='')
How do I use google cloud in sql?
What is indexes?
What are sql triggers used for?
What is sql injection owasp?
Is left join and outer join same?
Can we create table in function?
Who developed sql?
How do I find duplicates in sql?
What is INSTEAD OF trigger ?