The Microsoft SQL Server TOP clause limits the number of rows returned by a SELECT statement. TOP allows specifying the number or percentage of rows to return. If the SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set.

The Oracle ROWNUM pseudocolumn returns a number indicating the order in which the row was selected in the result set. ROWNUM can be used to limit rows in Oracle, but ROWNUM is assigned before ordering.

SQLWays converts the Microsoft SQL Server TOP clause to Oracle as follows:

a)SELECT statement with TOP does not contain the ORDER BY clause.

If Microsoft SQL Server does not contain the ORDER BY clause, SQLWays implements the TOP clause using the ROWNUM pseudo column in the WHERE clause of the SELECT statement in Oracle.

If TOP is specified with the PERCENT clause SQLWays calculates the total number of rows retuned by the query and the number of rows corresponding to the specified percentage.

TABLE 25. SELECT statement with TOP does not contain the ORDER BY clause

Microsoft SQL Server	Oracle
create procedure sql_sp_select_top as DECLARE @a number select top 1 @a=col1 from tab1	CREATE OR REPLACE PROCEDURE sql_sp_select_top AS v_a VARCHAR2(255); BEGIN select col1 INTO v_a from tab1 WHERE ROWNUM <=1; END;
create procedure sql_sp_select_top2 as DECLARE @a number select top 1 @a=col1 from tab1 WHERE col2>0	CREATE OR REPLACE PROCEDURE sql_sp_select_top2 AS v_a VARCHAR2(255); BEGIN select col1 INTO v_a from tab1 WHERE col2>0 and ROWNUM <=1; END;
create procedure sql_sp_select_top3 as DECLARE @a number select top 30 percent @a=col1 from tab1	CREATE OR REPLACE PROCEDURE sql_sp_select_top3 AS v_a VARCHAR2(255); BEGIN select col1 INTO v_a from tab1 WHERE ROWNUM <= 30 *(SELECT COUNT(*) from tab1) / 100; END;

b) SELECT statement with TOP also contains the ORDER BY clause.

Unlike Microsoft SQL Server, Oracle applies comparison with ROWNUM before ordering the result set. If the Microsoft SQL Server SELECT statement contains the ORDER BY clause, SQLWays converts the source query to the query with the subquery. The subquery performs the ordering, while the query performs row restricting using ROWNUM.

TABLE 26. SELECT statement with TOP also contains the ORDER BY clause

Microsoft SQL Server	Oracle
create procedure sql_sp_select_top4 as DECLARE @a number select top 1 @a=col1 from tab1 order by col1	create or replace procedure sql_sp_select_top4 as a number; begin select * into a from (select col1 from tab1 order by col1) where rownum<=1; end;
create procedure sql_sp_select_top5 as DECLARE @a number select top 15 percent @a=col1 from tab1 order by col1	create or replace procedure sql_sp_select_top5 as a number; begin select * into a from (select col1 from tab1 order by col1) where rownum<=15*(select count(*) from tab1 order by col1)/100; end;