Query for retrieving top two rows from a table in SQL Server

开发者_如何学编程I have a table called Employee with the following fields:

• EmpID
• Salary
• Name

I want to get top two employees with maximum salary. How do I write this query ?

SQL Server 2000+:

  SELECT TOP 2
         e.*
    FROM EMPLOYEE e
ORDER BY e.salary DESC

MySQL & Postgres:

  SELECT e.*
    FROM EMPLOYEE e
ORDER BY e.salary DESC
   LIMIT 2

Oracle:

SELECT x.*
  FROM (SELECT e.*,
               ROWNUM as rn
          FROM EMPLOYEE e
      ORDER BY e.salary DESC) x
 WHERE x.rn <= 2

• Oracle: ROW_NUMBER vs ROWNUM

Try this ..

SELECT * from Employee  order by Salary  desc limit 2 ;

SELECT TOP 2 * FROM Employee ORDER BY Salary DESC;

You should write something like this.

SELECT TOP 2 EmpID,Salary,Name FROM Employee ORDER BY Salary

Yet another solution:

With NumberedItems As
    (
    Select EmpId, Salary, Name
        , Row_Number() Over ( Order By Salary Desc ) As SalaryRank 
    From Employee
    )
Select EmpId, Salary, Name
From NumberedItems
Where SalaryRank <= 2