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: 289
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
