Geek asks and answers

SQL query to find nth highest salary

I am referring to the following query to find the Nth highest employee salary.

select sal from emp t where &n = (select count(sal) from (select distinct sal 
 from emp) where t.sal<=sal);

One gentleman said this request works. Can someone explain how to equate COUNT (which will really cost from 1 to X, where X are the total individual salaries) to get this result?

I am trying to understand how the database processes this query internally and produces the result?

Thank.

+3
source share
9 answers

First, the query will return the nth lower salary value . To return a nthhigher salary value , you must change t.sal <= salto t.sal >= sal.

, , , , . t.sal <= sal ( ) emp. , .

:

Alice       | 200
Bob         | 100
Charlie     | 200
Danielle    | 150

Select Distinct sal
From emp


200
100
150


Alice - There are 3 distinct salary values less than or equal to 200
Bob - 1 rows <= 100
Charlie - 3 rows <= 200
Danielle - 2 row <= 150

, ( ):

Bob 1
Danielle 2
Charlie 3
Alice 3

, , , , , emp ( ). I.e., emp t.sal <= sal. , ( As Z):

Select sal
From emp As t
Where &n =  (
            Select Count(Z.sal)
            From    (
                    Select Distinct sal
                    From emp
                    ) As Z
            Where t.sal <= Z.sal
            )
+8

n- , "N".

Select Min(Salary) From (Select Top N * From Table_Name Order by Salary Desc);
+1
select sal 
from (
  select sal, 
         dense_rank() over (order by sal desc) as rnk
) t
where rnk = 5;

where rnk = 5 "nth", .

+1
  SELECT Max(Salary) as Salary
    FROM employee
    where  Salary Not in 
    (SELECT TOP N Salary FROM employee ORDER BY Salary DESC)
  where N is defined by you.

, , : employeeID Salary - .

EmployeeID

 101  25,000
 154  89,000
 987  42,000
 450  12,000
 954  50,000


25,000

.

0
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always
0

,

employ_id    NAME     salary
101          Henry    24000
102          Smith    24000
105          Roy      17000  
106          Robbin   15000 
702          Mac      2500
708          Bill     2100
709          Kane     2000
710          Ted      2000

, , n- ( / )

3-

select * from emloyees where salary in (select salary from (select rownum rank , salary from (select distinct salary from employees order by salary **desc**)) where rank =3;

ans = 15000

3- desc type asc

select * from emloyees where salary in (select salary from (select rownum rank , salary from (select distinct salary from employees order by salary **asc**)) where rank =3;

,

0

n- , "N"

SELECT e1.EmployeeName, e1.EmployeeSalary from Employee e1
where N = (
select COUNT(e2.EmployeeSalary) from Employee e2 where e2.EmployeeSalary >= e1.EmployeeSalary)
0

: -

1)

 Select Top(1) sal from emp 
    where sal not in (select DISTINCT top(n-1) sal from emp order by sal desc)

2)

select salary     
          from (
           select salary,
           roe_number() over (order by salary ) as row from emp
          ) emp1
  where row= n;
  • , .

3)

select salary     
              from (
               select salary,
               dense_rank() over (order by salary ) as row from emp
              ) emp1
      where row= n;
  • This will create a unique line number for all unique salary amounts.

4)

 Select Min(sal) From 
       (Select DISTINCT Top n * From emp Order by sal Desc)as emp1;

5)

   SELECT * FROM emp Emp1
            WHERE (n-1) = (
                             SELECT COUNT(DISTINCT(Emp2.Sal))
                             FROM emp Emp2
                             WHERE Emp2.Sal > Emp1.Sal)
0
source

Query:

select 
    ename  
    ,sal  
    ,dense_rank() over (order by sal desc) ranking  
from   emp;

output:

ENAME   SAL   RANKING
KING    5000    1   
FORD    3000    2  
SCOTT   3000    2  
JONES   2975    3  
CLARK   2850    4  
BLAKE   2850    4  
ALLEN   1600    5

Wrap the filter around and select the highest salary Nth, say the fourth highest salary.

Query:

select *  
from  
(  
  select ename  
        ,sal  
        ,dense_rank() over (order by sal desc) ranking  
  from   emp  
)  
where ranking = 4 -- Replace 4 with any value of N

output:

ENAME  SAL  RANKING
BLAKE  2850     4  
CLARK  2850     4
-1
source

More articles:


All Articles