CODEWITHSUNDEEP
sqloracle-database
HOLYBIBLETHE
HOLYBIBLETHE

Reputation: 153

Can someone suggest innovative queries for selecting the 2nd highest salary from a table of employees?

Can someone suggest innovative queries for selecting the 2nd highest salary, from a table "EMP" which consists of three "NOT NULL" fields, sal(salary), name, empno(employee number) ?

I have one:

Select sal 
from EMP A 
where 1 = (select count(DISTINCT SAL) from EMP B whre A.sal < B.sal);

select DISTINCT sal 
from emp a 
where 1 = (select count(DISTINCT sal) from emp b where a.sal < b.sal);

Upvotes: 0

Views: 1114

Answers (8)

ravali
ravali

Reputation: 1

;WITH cte
AS (
    SELECT *,
        ROW_NUMBER() OVER (
            ORDER BY [salary] DESC
            ) AS rowid
    FROM [Employee].[DimEmployeeDetails]
    )
SELECT *
FROM cte
WHERE rowid = 2

Upvotes: -1

Devbrat Tripathi
Devbrat Tripathi

Reputation: 1

This is most favorable interview question. You can solve this question from different ways- Query 1:

select MAX(Sal) from Employee WHERE Sal NOT IN (select MAX(Sal) from Employee );

Query 2:

WITH CTE AS (
SELECT  Ename, sal, 
ROW_NUMBER() OVER(ORDER BY sal DESC) as RN
FROM Employee
)
SELECT Ename, sal
FROM CTE
WHERE RN = 2

Query 3: If you want to get the Nth highest salary, you just need to mention N(1/2/3/4/..N) in place of 2.

select A.Ename, A.sal from Employee A
where 2 = (select count(*) from Employee B where B.EmpNo >= A.EmpNo)

You can find more answers [here]. https://knowledgebase.techandmate.com/answers/55

Thanks,

Upvotes: 0

silambu
silambu

Reputation: 1

SELECT MAX(esalary)
FROM emp 
WHERE esalary < 
(SELECT MAX(esalary)
FROM emp); 

SELECT MAX(esalary) 
FROM emp 
WHERE esalary NOT IN 
(SELECT MAX(esalary)
FROM emp);

Upvotes: 0

vry
vry

Reputation: 16

select top (1) sal from Emp where sal<(select max(sal) from emp) order by sal desc

In above query first we are sorting all the salaries in descending order so as to get the highest salary at the top and then we are selecting top salary which is less than the highest salary in the column.

Upvotes: 0

vry
vry

Reputation: 16

SELECT * FROM EMP A
WHERE 2= (SELECT COUNT(*) FROM EMP B WHERE B.SAL >=A.SAL)

If the second highest salary is present for 2 employees, then instead of using 2, use 3 after where clause.

Upvotes: 0

Rakesh Anand
Rakesh Anand

Reputation: 423

Query to find 2nd Highest salary.

SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMP));

Upvotes: 0

Vivekanand S V
Vivekanand S V

Reputation: 2363

This was already answered in SOF, you have to decide whether they are innovative enough for your needs.

What is the simplest SQL Query to find the second largest value?

How to get second largest or third largest entry from a table

SELECT emp_salary,R 
  FROM (SELECT ROWNUM R,emp_salary 
          FROM (SELECT emp_salary 
                  FROM Employee 
                 ORDER BY emp_salary DESC
               )
        ) 
 WHERE R=2

That should work as you wanted. You can replace the value of R for finding any value. R=2 gives 2nd highest, R=3 gives 3rd highest.

You can try out the fiddle here http://sqlfiddle.com/#!4/a6f42/14

Upvotes: 0

user330315
user330315

Reputation: 

select empno, 
       name,
       salary
from (
   select empno, 
          name,
          salary, 
          dense_rank() over (order by salary desc) as rnk
   from employee
) t
where rnk = 2;

Upvotes: 3

Related Questions