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



How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

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

How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

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

How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

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

How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

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

How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

Answer / shriram

You can also do it by the following query ..

select * from (select name,salary,rank() over(order by
salary desc as r) from employee) where r = 2;

The above query returns the 2nd highest salary from the table.

Is This Answer Correct ?    3 Yes 2 No

How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

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

How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

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

How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

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

How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

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

How to retrieve a second highest salary from a table? Note:Suppose salaries are in duplicate valu..

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

Post New Answer

More SQL PLSQL Interview Questions

What are nested triggers ?

6 Answers   Amazon, Appeal Soft, IBM, Infosys, TCS,


Can a table have two primary keys?

44 Answers   HCL, MetaCube,


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='')

2 Answers  


How do I use google cloud in sql?

0 Answers  


What is indexes?

0 Answers  






What are sql triggers used for?

0 Answers  


What is sql injection owasp?

0 Answers  


Is left join and outer join same?

0 Answers  


Can we create table in function?

0 Answers  


Who developed sql?

0 Answers  


How do I find duplicates in sql?

0 Answers  


What is INSTEAD OF trigger ?

13 Answers   Hexaware, TCS,


Categories