How To Find The Highest Salary In SQL Server 2017

Introduction

In this article, I am going to explain how to find the highest salary using various methods. This is the one of the most common questions asked in an interview in SQL.

Below are a couple of ways of finding the Highest Salary.

Find the highest salary in SQL Server using a Sub-Query
Find the highest salary in SQL Server using a CTE

Here we will be using SQL Server 2017 or you can use SQL Server 2008 or above.

Read my previous Joins in SQL Server 2017 part of this article using the below links before reading this article,

Prerequisites

SQL Server 2017 or you can use SQL server 2008 or above version.

Now, first we will create a Database and a table.

Creating Database and One Table

Step 1 - Create a Database

Open your SQL Server and use the following script to create the “chittadb” Database.

Create database chittadb

Now, select the script query then press F5 or click on Execute button to execute the above script.

You should see a message, “Command(s) completed successfully.” Means your new database created.

Step 2 - Create a table

Open your SQL Server and use the following script to create table “tbl_Employees”.

Create table tbl_Employees
(
Id int primary key not null identity(1,1),
FirstName varchar(50),
LastName varchar(20),
Location varchar(20),
Gender varchar(50),
Salary int
)

Execute the above query to create “tbl_Employees “.

You should see a message, “Command(s) completed successfully.”

Now, data is inserted into the table.

Insert into tbl_Employees values ('Chittaranjan', 'Swain','Odisha', 'Male', 80000)
Insert into tbl_Employees values ('Chandin', 'Swain', 'Pune','Female', 76000)
Insert into tbl_Employees values ('Mitu', 'Pradhan','Delhi', 'Male', 55000)
Insert into tbl_Employees values ('Jeni', 'Swain','Chennai', 'Female', 76000)
Insert into tbl_Employees values ('Adyashree', 'Swain','UK', 'Female', 49000)
Insert into tbl_Employees values ('Ram', 'Kumar','US', 'Male', 39000)
Insert into tbl_Employees values ('Jitendra', 'Gouad','Hydrabad', 'Male', 35000)
Insert into tbl_Employees values ('Dibas', 'Hembram','Bangalore', 'Male', 55000)

Execute the above query, you should see a message, “Command(s) completed successfully.”

Now retrieve all data from “tbl_Employees” table.

select * from tbl_Employees

output

To Find The Highest Salary

We can simply use the Max() function as shown below.

Select Max(Salary) as Salary from tbl_Employees

OutPut

To Find The Lowest Salary

We can simply use the MIN() function as shown below.

Select MIN(Salary) as Salary from tbl_Employees

OutPut

To Find The Highest Salary Using Sub-Query,

SELECT TOP 1 SALARY
FROM (
SELECT DISTINCT TOP 1 SALARY
FROM tbl_Employees
ORDER BY SALARY DESC
) RESULT
ORDER BY SALARY

OutPut

To Find The Highest Salary Using CTE,

WITH RESULT AS
(
SELECT SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
FROM tbl_Employees
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = 1

OutPut

Conclusion

In this article, I explained how to find the highest salary with several ways in SQL Server with examples. I hope this article has helped you to understand this topic. Post your valuable feedback in the comments section.