Alternative to TOP in SQL Server and Oracle.

Posted on Microsoft .NET Support Team See other posts from Microsoft .NET Support Team
Published on Mon, 05 Apr 2010 12:18:00 +0000 Indexed on 2011/01/11 9:57 UTC
Read the original article Hit count: 184

Filed under:
|
|

SELECT TOP 5 * FROM EMP ORDER BY SALARY;

Above query works in SQL Server. This returns top 5 employees. The problem with this query is it doesn't work with Oracle. In Oracle you would need to write the query as follows.

SELECT * FROM EMP WHERE ROWNUM<=5 ORDER BY SALARY
If you are looking for a query which runs in both Oracle and SQL Server. Please use below one.
select * from (SELECT row_number() over( ORDER by SALARY) as rank, EMP.* FROM EMP) s1 where s1.rank <= 5;

© Microsoft .NET Support Team or respective owner

Related posts about database

Related posts about Oracle