finding second highest salary with two records with same

Question

while finding second highest salary i have two records with same salary i want to retrive both

Jayesh Vaghasiya · Accepted Answer

try this   DECLARE @HIGHESTSALARYRANK INT = 2 DECLARE @TBL TABLE(ID INT,NAME VARCHAR(32),SALARY NUMERIC(18,2)) INSERT INTO @TBL SELECT 1 ,'Ajay' , 500.00 INSERT INTO @TBL SELECT 2 ,'vjay' , 700.00 INSERT INTO @TBL SELECT 3 ,'Naresh', 900.00 INSERT INTO @TBL SELECT 4 ,'suresh', 500.00 INSERT INTO @TBL SELECT 5 ,'anup' , 700.00 INSERT INTO @TBL SELECT 6 ,'nagesh', 300.00 SELECT ID,NAME,SALARY FROM (SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) ROWNO, * FROM @TBL )FINAL WHERE ROWNO = @HIGHESTSALARYRANK

Suraj Kumar · Answer

You can use CTE also as shown below. ; WITH  T  AS    (   SELECT  *,          DENSE_RANK() OVER ( ORDER   BY  Salary  Desc )  AS  Rnk   FROM  Employees   )   SELECT  *   FROM  T   WHERE  Rnk=2;

Vishal Agarwal · Answer

You can use nested query to find 2nd highest Salary of the Emplyee, select Max(Salary) as Salary from employee where  salary   You can do same for finding 3rd Highest Salary of the Employee with One more Nested Query same.

Suraj Kumar · Answer

It has already written in query as in variable 700 will be assigned as second hightest salary then below query of select statement will give all records of 700. Request you to run that query. You can see the attached query output with sample data in my table.

ajay kumar · Answer

I don't want to take distinct
1  Ajay      500.00
2  vjay      700.00
3  Naresh  900.00
4  suresh   500.00
5  anup     700.00
6  nagesh  300.00
this is my table here i have 700 two timeso ,i have to retrive those two records.

Suraj Kumar · Answer

Hello Ajay,   try the below sql query DECLARE  @2ndHighestSalary  INT   -- Creating A variable    --Finding Second Highest Salary and Assingning Values to variable    SET  @2ndHighestSalary = (  SELECT   TOP  1 *   FROM  (   SELECT   DISTINCT   TOP  2 salary   FROM  Employee   ORDER   BY  salary  DESC    )  AS   temp    ORDER   BY  salary   );   --Select All details of Employee Records of that salary    select  *  from  Employee  where  Salary = @2ndHighestSalary   here you can also use:   Salary = to find the employee whose salary is greater than the second highest salary.   Here in above query 2 is for Second highest if you want to find 3rd highest then place only 3 in place of 2.   Hope this will help you.