CodeKicks.com
Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Monday, April 05, 2010

Alternative to TOP in SQL Server and Oracle.

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;

Post a Comment