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
2010/04/05
12:53 UTC
Read the original article
Hit count: 230
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;
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
