LINQ Query to find nth Highest Salary

In this post, I will explain a LINQ Query to find nth Highest Salary. 

In below LINQ query subquery returns the count of distinct salary greater than current employee salary. Then the result of subquery will be compared with N and record with subquery result count equal N will be returned as result.

int N = 2;
var employees = db.Employees
               .Where(em1 =>
                        db.Employees.Select(em2 => new { em2.Salary })
                        .Where(em2 => em2.Salary > em1.Salary)
                        .Distinct().Count().Equals(N)
                       );

            GridView1.DataSource = employees;
            GridView1.DataBind();


Table Script-

CREATE TABLE Employee(
 EmployeeId int IDENTITY(1,1) ,
 EmployeeName varchar(100) NULL,
 Salary decimal(18, 2) NULL,
) 

Read More

SQL Query to find nth Highest salary

In this post, I will explain a SQL Query to find nth Highest salary. 

In below query subquery returns the count of a distinct salary greater than current employee salary. Then the result of subquery will be compared with @N and record with subquery result equal @N will be returned as result.

DECLARE @N AS int = 2

SELECT * FROM Employee_Master EM1
WHERE @N  = (SELECT COUNT(DISTINCT(EM2.Salary)) FROM Employee_Master EM2 WHERE EM2.Salary > EM1.SALARY)

Table Script-

CREATE TABLE Employee_Master(
EmployeeId int IDENTITY(1,1) ,
EmployeeName varchar(100) NULL,
Salary decimal(18, 2) NULL,
) 

Read More