CODEWITHSUNDEEP
sqlsql-server
mevr
mevr

Reputation: 1135

Get employee with third highest salary

I need to fetch all details of employee with third highest salary in the most efficient way.

My query:

select(SELECT MIN(Salary)
FROM   (SELECT * TOP (3) Salary
       FROM   Employees
       ORDER  BY Salary)

Is there any issue in my query.how can i correct my query.Please help.

Upvotes: 2

Views: 854

Answers (3)

square_particle
square_particle

Reputation: 524

There are a few ways that you can go about doing this. Two possible ways are

  • use row_number (or another windowing function, e.g. rank or dense_rank)
  • use top on a subquery and then subsequntly on the result set

I would suggest not using row_number, since that would require more cpu, and, instead use the latter of the above two approaches.

Below are test cases for both, and what is shown is that the subquery approach runs in less than a quarter of the time.

create table #Salary (
    PersonId int identity(1, 1) primary key clustered,
    SalaryAmt decimal(16, 2) not null);

/* Insert some test data ... */
insert #Salary (
    SalaryAmt)
select top (10000000)
    abs(checksum(newid()) % 1000000)
from sys.all_columns a
cross join sys.all_columns b

set statistics io on;
set statistics time on;

select *
from (
    select *,
        RowNum = row_number() over (order by SalaryAmt desc)
    from #Salary) a
where RowNum = 3;

set statistics time off;
set statistics io off;

checkpoint;
go
dbcc dropcleanbuffers;
go

set statistics io on;
set statistics time on;

select top 1 *
from (
    select top 3 *
    from #Salary
    order by SalaryAmt desc) top_3
order by SalaryAmt;

set statistics time off;
set statistics io off;

The results are below:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
PersonId    SalaryAmt                               RowNum
----------- --------------------------------------- --------------------
1427822     999999.00                               3

Table '#Salary_____________________________________________________________________________________________________________000000000007'. Scan count 9, logical reads 27219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 SQL Server Execution Times:
   CPU time = 17123 ms,  elapsed time = 2361 ms.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
PersonId    SalaryAmt
----------- ---------------------------------------
1427822     999999.00

Table '#Salary_____________________________________________________________________________________________________________000000000007'. Scan count 9, logical reads 27219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 SQL Server Execution Times:
   CPU time = 4282 ms,  elapsed time = 570 ms.

Hope this helps.

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28895

You can use Rownumber:Here i partitioned by empid to avoid ties

;With cte
as
(
select *,row_number() over (partition by empid order by salary desc) as rownum
from 
table
)
select * from cte where rownum=3

if you want to use your query: 

SELECT MIN(Salary
FROM   (
SELECT  TOP (3) Salary
       FROM   Employees
       ORDER  BY Salary
)b

Upvotes: 1

reza.cse08
reza.cse08

Reputation: 6178

use RANK

; WITH T AS
(
   SELECT * , RANK() OVER (ORDER BY SalaryDESC) as rk
   FROM Employees 
)    
Select TOP(1) *
FROM T
WHERE T.rk=3

Upvotes: 2

Related Questions