How To Find The Highest Salary In SQL Server

Introduction

In this blog we will discuss how to find the highest salary, the second highest salary and N number of the highest salaries with different examples.

Step 1

Create table in SQL server and insert some data.

  1. CREATE TABLE [dbo].[Employee](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Position] [nvarchar](50) NULL,  
  5.     [Office] [nvarchar](50) NULL,  
  6.     [Salary] [int] NULL,  
  7.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED   
  8. (  
  9.     [ID] ASC  
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  11. ) ON [PRIMARY]  
  12.   
  13. GO  

Step 2

Write a query to select all records from the table:

SELECT*FROM EMPLOYEE

SQL SERVER 

Query to find the highest salary
  1. SELECT*FROM  [DBO].[EMPLOYEE] ORDER BY SALARY DESC  
  2.   
  3. SELECT MAX(SALARY) FROM EMPLOYEE   
SQL SERVER 
 
Query to find second highest salary
  1. SELECT*FROM EMPLOYEE ORDER BY SALARY DESC  
  2.   
  3. SELECT MAX(SALARY) FROM EMPLOYEE   
  4. WHERE SALARY<(SELECT MAX(SALARY) FROM EMPLOYEE)  
SQL SERVER 
  1. SELECT*FROM EMPLOYEE ORDER BY SALARY DESC  
  2.   
  3. SELECT DISTINCT TOP 2 SALARY  
  4. FROM EMPLOYEE  
  5. ORDER BY SALARY DESC  
 SQL SERVER
 
 Nth number of highest salary using DENSE_RANK function
  1. SELECT SALARY,  
  2.            DENSE_RANK() OVER (ORDER BY SALARY DESC) AS SALARY_RANK  
  3. FROM EMPLOYEE  
SQL SERVER 
 
 TO FIND NTH HIGHEST SALARY USING CTE
  1. SELECT*FROM  [DBO].[EMPLOYEE] ORDER BY SALARY DESC  
  2. GO  
  3.   
  4. WITH RESULT AS  
  5. (  
  6.     SELECT SALARY,  
  7.            DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK  
  8.     FROM EMPLOYEE  
  9. )  
  10. SELECT TOP 1 SALARY  
  11. FROM RESULT  
  12. WHERE DENSERANK = 3  
SQL SERVER