Optimized top 2 ways to find 2nd or Nth highest salary in SQL Server.

Today we will understand how to find the Nth highest salary in an optimized way and the Nth highest salary in each department using the SQL server.

There are lots of the solution to find Nth highest salary and department wise Nth highest salary but the question is that what is the optimized solution to find Nth salary.

This is the most popular interview question in the SQL Server database. Don't worry I'm sharing the best optimized solution to find the Nth highest salary from the Employee table and also find the Nth highest salary in each department.

[Top 100+ popular SQL Interview Questions and Answers Blog.]

There are two optimized solutions to finding nth highest salary from the Employee table. One is using CTE (Common Table Expression) and the second is using a subquery. Let's see both solutions with examples.

First, create the department table and insert some dummy data.

CREATE TABLE [dbo].[Department]

(

       DeptId INT IDENTITY(1,1) PRIMARY KEY,

       DeptName VARCHAR(100)

);

GO

INSERT INTO [dbo].[Department] (DeptName) VALUES ('Computer');

INSERT INTO [dbo].[Department] (DeptName) VALUES ('Research');

INSERT INTO [dbo].[Department] (DeptName) VALUES ('Sales');

INSERT INTO [dbo].[Department] (DeptName) VALUES ('Accouting');

Now, We create the Employee table and insert some dummy data.

CREATE TABLE [dbo].[Employee]

(

       EmpId INT IDENTITY(1,1) PRIMARY KEY,

       EmpName VARCHAR(20),

       ManagerId INT NULL,

       Salary MONEY,

       DeptId INT FOREIGN KEY REFERENCES [dbo].[Department](DeptId)

);

GO

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Rajesh',NULL, 60000,1);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Rohan',1, 50000,1);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Janak',NULL, 70000,2);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Mohan',3, 50000,2);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Rahul',NULL, 40000,3);

INSERT INTO [dbo].[Employee] (EmpName,ManagerId,Salary,DeptId) VALUES ('Pragnesh',NULL, 40000,4);

 

Hope you can successfully able to create both the above tables in your database now we go for example.

Actual employee data in the employee table.

SELECT * FROM Employee ORDER BY Salary DESC

1) Find the 2nd highest salary or the Nth highest salary using CTE (Common Table Expression).

Example of 2Nd highest salary.

DECLARE @Rank INT=2;

 

WITH EmployeeCte AS

(

       SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) As SalaryRank,* FROM Employee

)

SELECT * FROM EmployeeCte WHERE SalaryRank=@Rank

Result

Example of Nth highest salary with CTE.

DECLARE @Rank INT=N;

 

WITH EmployeeCte AS

(

       SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) As SalaryRank,* FROM Employee

)

SELECT * FROM EmployeeCte WHERE SalaryRank=@Rank

In the above two examples, you can clearly be seen that if we set the @Rank=2 it will return 2nd highest salary of the employee or we set 3 then it will return 3rd highest salary. This is 

1) Find the 2nd highest salary or the Nth highest salary using a subquery.

DECLARE @Rank INT=2;

SELECT * FROM Employee Emp1 WHERE (@Rank-1)=

(

SELECT COUNT(DISTINCT(Salary)) FROM Employee Emp2 WHERE Emp2.Salary>Emp1.Salary

)

Result

If you can see here we have got the same result of the query, but the question is that which is an optimized solution, so as per my suggestion option 1 using CTE is the best option and also easy to implement for the developers. Because the 2nd solution traverses each row of the employee table and compares with the inner query so it will take much time compared to solution 1.

Now we find nth highest salary department wise from the employee table.

The nth highest salary department wise

Example of 2nd highest salary department wise

DECLARE @Rank INT=2;

WITH EmployeeCte AS

(

       SELECT DENSE_RANK() OVER (PARTITION BY DeptId ORDER BY Salary DESC) As SalaryRank,* FROM Employee

)

SELECT

       E.EmpId

       ,E.EmpName

       ,E.Salary

       ,D.DeptName

FROM

       EmployeeCte E

       INNER JOIN Department D ON E.DeptId=D.DeptId

WHERE 

        SalaryRank=@Rank

Result

Using this query you can find 3rd, 4th,5th and Nth highest salary from the employee table and also you can find the department wise highest salary in an optimized way. Also, you can find the Nth lowest salary by changing the order with ASC.

I hope you have liked this article to the understanding of Find the Nth highest salary or 2nd highest salary and Department wise Nth highest salary.

Please feel free to comment if you have found anything incorrect, or you want to share more information about the topic discussed above.

Please Share this article so it will help to another one because “Helping millions grow better”.

Follow my blog https://mydotnetguru.blogspot.com to read the more interesting technical articles.

Next Recommended Article:

1. Different ways to remove or delete duplicate rows from a SQL Table.
   
2. When to use abstract class and interface in C#?
3. Async and Await in C#
   
4. Top 100+ popular ASP.Net MVC Interview Questions and Answers.
   
5. Top 100+ popular SQL Interview Questions and Answers Blog.
6. Top 100+ popular C# Interview Questions and Answers.
   
7. @Input, @Output decorator and EventEmitter class in Angular.
8. Dependency Injection and types of dependency injection.

Async and Await in C#

Understanding of Async and Await Keywords in C# and how to achieve asynchronous programming mechanism in C#.

Today we will understand of Async and Await in C# and also want to clarify some myths about Async.

During the interview most of us faced questions like, what is Async and Await in C#, why we use Async and Await in C#?, how Async and Await are work in C#. Even in real projects lots of developers using this keyword but some of them don’t know all about this keyword. I’m telling some of them may be possible all of them know about these keywords, so please forgive me if you know. In this article, I will give all the answers to the most of questions regarding Async and Await keywords. 

[Top 100+ popular C# Interview Questions and Answers.]

Definition of Async and Await

Async and Await are the code markers, which indicates where the control should resume after task completion.

Async and Await Key points

1. Async and Await are the code markers, which indicate where the controller should resume after the task completion.

2. Introduce in .Net Framework 4.5.

3. We can run all the methods parallels using Async and Await keyword.

4. In the traditional method, we are using Thread mechanisms to achieve parallel processing but it will block the whole UI until all processes not done. We can resolve this problem using simple Async and Await keyword.

5. If method one is dependent on method two, in that case, the Await keyword is waiting for the completion of method one after that method two will start.

6. We can’t use Async without the Await keyword or vise-versa in the code.

When we use Async and Await?

In real-application when there are long-running tasks like reading or writing files or any other task, in that case, we use an asynchronous programming mechanism. We write our code within the Async method and call them into the main class so it will work parallel with other methods in the main class. We will look ahead with code examples for the same.

How Async and Await work?

Async will run your long task parallelly with other tasks. It will divide your code into a chunk of code. Compiler processing parallels on this chunk of code and produces output. Please keep in mind Async never creates a new thread for the task, so many developers think that Async assigns a new thread to the task but that is totally wrong thinking. The Async task or method runs on the current synchronization context and uses time on the thread only when the method is active. There is only a working thread is generated for resume control on to main thread after completion of the task.

Advantage of Async and Await

The main advantage of using Async and Await is to overcome the problem of GUI blocking. In a traditional threading mechanism, the main thread is also blocking during the asynchronous process. Using Async and Await, asynchronous tasks work separately without blocking the main thread and that is the reason the user can access the GUI.

Examples of C# Async and Await

class Program

    {

        static void Main(string[] args)

        {

            Method1();

            Console.WriteLine("Main thread.");

            Console.ReadLine();

        }

        public static void Method1()

        {

            Task.Run(new Action(LongTask));

            Console.WriteLine("Long task over.");

        }

        public static void LongTask()

        {

            for (int i = 0; i < 10; i++)

            {

                Console.WriteLine(i);

            }

        }

    }

Output:

Here we can clearly see that the controller doesn’t wait for the completion of a long task and move for the next task in the code.

Now suppose some method is dependent on long-running methods output, then how can we achieve?

Let’s modify the same example with Async and Await keyword, and look at what’s happening.

        static void Main(string[] args)

        {

            Method1();

            Console.WriteLine("Main thread.");

            Console.ReadLine();

        }

        public async static void Method1()

        {

            await Task.Run(new Action(LongTask));

            Console.WriteLine("Long task over.");

        }

        public static void LongTask()

        {

            for (int i = 0; i < 10; i++)

            {

                Console.WriteLine(i);

            }

        }

Output:

Here we can clearly see that the compiler is waiting for the completion of a long-running task after that move forward to the next task. During this process, the main thread is a non-blocking state so the user can access the GUI during this asynchronous process. Using the Await keyword we can wait until the result of a long-running task.

Myth of C# Async and Await.

Some of the developers think that asynchronous process produces new threads, but in actual the asynchronous process never create or produce new threads please keep in mind. As I have mentioned above, The Async method runs on the current synchronization context and uses time on the thread only when the method is active. There is only a working thread is generated for resume control on to main thread after completion of the task.

I hope you have liked this article to the understanding of Async and Await in C#.

Please feel free to comment if you have found anything incorrect, or you want to share more information about the topic discussed above.

Please Share this article so it will help to another one because “Helping millions grow better”.

Follow my blog https://mydotnetguru.blogspot.com to read the more interesting technical articles.

Next Recommended Article:

1. When to use abstract class and interface in C#?
   
2. Optimized top 2 ways to find 2nd or Nth highest salary in SQL Server.
3. Different ways to remove or delete duplicate rows from a SQL Table.
4. Top 100+ popular ASP.Net MVC Interview Questions and Answers.
   
5. Top 100+ popular SQL Interview Questions and Answers Blog.
6. @Input, @Output decorator and EventEmitter class in Angular.
7. Dependency Injection and types of dependency injection.