How To Find The Second Highest Salary In SQL Server?

Today, We want to share with you second highest salary in sql server.In this post we will show you how to find highest salary in sql using joins, hear for nth highest salary in sql using dense_rank we will give you demo and example for implement.In this post, we will learn about SQL Subquery, IN SELECT – With Examples with an example.

how to find second highest salary in sql using subquery?

here we learn to step by step 5+ Ways to find Nth highest salary in SQL – MySQL Example with demo bellow.

Creating a Database and One Table

Step 1 – Create a Database

Create database pakainfo  

Step 2 – Create a table

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

create “tbl_Members “.

Insert into tbl_Members values ('Chirag', 'Rajkoitn','India', 'Male', 80000)  
Insert into tbl_Members values ('Meera', 'Rajkoitn', 'Pune','Female', 76000)  
Insert into tbl_Members values ('Mayur', 'Pradhan','Delhi', 'Male', 55000)  
Insert into tbl_Members values ('Pankit', 'Rajkoitn','Chennai', 'Female', 76000)  
Insert into tbl_Members values ('Jayshree', 'Rajkoitn','IN', 'Female', 49000)  
Insert into tbl_Members values ('Radhika', 'Kumar','US', 'Male', 39000)  
Insert into tbl_Members values ('Mansukh', 'Gouad','Bhopal', 'Male', 35000)  
Insert into tbl_Members values ('Bhavika', 'Hembram','Bangalore', 'Male', 55000)  

retrieve all data from the “tbl_Members” table.

select * from tbl_Members  

Find Second Highest Salary?

select distinct top 2 salary from tbl_Members order by Salary desc  

Find Second Highest Salary Using a Sub-Query

Select Max(Salary) as Salary from tbl_Members where Salary <(select MAX(Salary) from tbl_Members)  

Find Second Highest Salary Using a Sub-Query

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

Find Second Highest Salary Using CTE

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

Find the Third Highest Salary

select distinct top 3 salary from tbl_Members order by Salary desc  

Find the Third Highest Salary Using Sub-Query

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

Find the Third Highest Salary Using CTE

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

Nth maximum salary in MySQL using LIMIT keyword

SELECT salary FROM Member ORDER BY salary DESC LIMIT N-1, 1

Nth highest salary in MySQL using LIMIT clause:

SELECT salary FROM Member  ORDER BY Salary DESC LIMIT n-1,1

I hope you get an idea about second highest salary in sql server.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.