0

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);
HOLYBIBLETHE
  • 153
  • 7

8 Answers8

3
select empno, 
       name,
       salary
from (
   select empno, 
          name,
          salary, 
          dense_rank() over (order by salary desc) as rnk
   from employee
) t
where rnk = 2;
  • Do you mean `dense_rank() over (order by salary)` to ensure there is still a `rnk = 2` if more than one person has the top salary? (And having `[dense_]rank(salary)` makes it the aggregate version, not the analytic version). – Alex Poole Jan 28 '13 at 13:47
  • Thanks for the `rank(salary)` hint - simple copy & paste error. And you are right `dense_rank()` is probably better. –  Jan 28 '13 at 13:55
0

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

Community
  • 1
  • 1
Vivekanand S V
  • 2,263
  • 1
  • 14
  • 14
0

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));
Rakesh Anand
  • 423
  • 6
  • 9
0
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.

Jeffrey Bosboom
  • 13,313
  • 16
  • 79
  • 92
vry
  • 1
  • 2
0

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.

Stedy
  • 7,359
  • 14
  • 57
  • 77
vry
  • 1
  • 2
0
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);
dakab
  • 5,379
  • 9
  • 43
  • 67
silambu
  • 1
0

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,

Devbrat Tripathi
  • 1
-1
;WITH cte
AS (
    SELECT *,
        ROW_NUMBER() OVER (
            ORDER BY [salary] DESC
            ) AS rowid
    FROM [Employee].[DimEmployeeDetails]
    )
SELECT *
FROM cte
WHERE rowid = 2
slavoo
  • 5,798
  • 64
  • 37
  • 39
ravali
  • 1