CODEWITHSUNDEEP
sql-server-2008-r2
Mohit
Mohit

Reputation: 9

Get Employee_Name, Basic_Salary of TOP 2 employees with highest Basic_Salary using sql server

╔════════╦══════════╦═════════╦════════════════╦
║ Emp_ID ║ EMP_Name ║ Dept_ID ║  Basic_Salary  ║  
╠════════╬══════════╬═════════╬════════════════╬
║      1 ║ Rahul    ║       1 ║           5000 ║  
║      2 ║ Raj      ║       2 ║           3000 ║  
║      3 ║ Amit     ║       1 ║           4000 ║  
║      4 ║ Sandeep  ║       1 ║           6000 ║  
╚════════╩══════════╩═════════╩════════════════╩ 

select name,basic_sal 
from emp 
where basic_sal >=(SELECT MAX(basic_sal) 
                   FROM emp 
                   WHERE basic_sal NOT IN (SELECT MAX(basic_sal) 
                                           FROM Emp ))

Upvotes: 0

Views: 49

Answers (2)

Tun Zarni Kyaw
Tun Zarni Kyaw

Reputation: 2119

For the normal case

SELECT TOP 2 * FROM employees ORDER BY Basic_Salary DESC

Updated:

In the case of more than two employees who are getting top 2 salary and want to get all those employees

SELECT * FROM employees 
WHERE Basic_Salary IN 
(SELECT TOP 2 Basic_Salary FROM employees ORDER BY DESC)

Upvotes: 1

Nagaraj S
Nagaraj S

Reputation: 13474

The SELECT TOP clause is used to specify the number of records to return.

The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

SELECT TOP 2 * FROM employees ORDER BY Basic_Salary DESC

Upvotes: 0

Related Questions