Get highest and lowest salary of an employee using Top,MAX and CTE in SQLServer

==>> One of most interview question Get  highest and lowest salary of an employee using Top and without Top ,Using Max without Max. So I am posting the all solution for get salary according question :----


EMP table:

SELECT * FROM dbo.EMP ORDER BY Salary

• Query for Get 3rd Highest and 3rd Lowest Salary using TOP Keyword in Sql Server

For Get 3rd Highest Salary :
 
SELECT TOP 1 EName,salary FROM (SELECT DISTINCT TOP 3 EName,salary FROM dbo.EMP ORDER BY Salary DESC) a ORDER BY salary ASC

For Get 3rd Lowest Salary
 
SELECT TOP 1 EName,salary FROM(SELECT DISTINCT TOP 3 EName,salary FROM emp ORDER BY Salary ASC  ) a ORDER BY salary DESC

• Query for Get 2rd Highest  using MAX in Sql Server

   

    SELECT MAX(Salary) as Salary
   FROM   dbo.EMP
   WHERE  Salary NOT IN ( SELECT  MAX(Salary)
                          FROM    dbo.EMP ) ;

  
  

•    Query for Get Nth Highest and Nth Lowest salary  using CTE in Sql Server

   

   WITH    Salaries
            AS ( SELECT   Salary ,
                          EName ,
                          ROW_NUMBER() OVER ( ORDER BY Salary ASC ) AS 'RowNum'
                 FROM     dbo.EMP
               )
      SELECT  Salary ,
              EName
      FROM    Salaries
      WHERE   RowNum = 5

   

  

   

   So in this way can select value using different-2 manner.The best way you can use CTE and find Nth number Value from table.

  
  

  Hope thei post will helpfull.....