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 · 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.