Thursday 29 August 2013

How to get second-highest salary employees in a table,Find 2nd highest salary in SQL server, SQL Query for second maximum salary from employee table

                                         Hi friends, in this article I will explain about Find 2nd highest salary in SQL server, How to get second-highest salary employees in a table, SQL Query for second maximum salary from employee table.
I will explain the SQL Query for second maximum salary from employee table.
Create one table as Employee table
CREATE TABLE Employee (EmpID  INT identity, EmpName  String, Salary as number )
And insert values.i.e insert usernames
INSERT Employee VALUES(“Roja” ,55000)
INSERT Employee VALUES(“Kishore” ,60000)
INSERT Employee VALUES(“Krish” ,40000)
INSERT Employee VALUES(“PurnaAnilKumar” ,50000)
INSERT Employee VALUES(“Satyanarayana” ,100000)
INSERT Employee VALUES(“Nageswari” ,40000)
Check how many rows inserted
SELECT * FROM Employee
It will like below table.
EmpID 
EmpName 
Salary
1
Roja
55000
2
Kishore
90000
3
Krish
40000
4
PurnaAnilKumar
50000
5
Satyanarayana
100000
6
Nageswari
40000

See the above table the second highest salary is 90000.Then we write the query for that.We can write different queries for this.
1. Using sub query
SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee)

2. Using below query we can get nth salary also.
SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employee ORDER BY salary DESC) AS EmpID  ORDER BY salary ASC

 3. Using Distinct keyword
Select min(salary) from Employee where salary in(Select distinct top 2 salary from Employee order by salary desc)

4. Using count
Select salary from Employee e where 2=(select count(distinct salary) from employee where e.salary<=slary)

 5. Simple query
Select Max(salary) from Employee where salary<(select max(salary from  employee)

 I think you like my blog why are waiting following me on facebook fan page Aspdotnet-Kishore

No comments:

Post a Comment

© 2012-2018 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.