So you have been coding away T-SQL queries for a while now and know that by using the
TOP keyword you can select the first what’s-the-number rows of your result set. Suddenly you find yourself in a familiar SQL environment, less the look and feel of the SQL Management Studio, with the exception that PL/SQL is somewhat different from T-SQL. So, how do you select only a limited number of rows from the dataset returned by your query?
In T-SQL, you would write a query like this:
SELECT TOP 100 * FROM Users WITH(NOLOCK) WHERE Active = 1 ORDER BY DateAdded
TOP 100 takes care of returning only the first 100 users that are active in T-SQL. Try to execute this statement in Oracle SQL Developer and you’ll get a syntax error out right. PL/SQL, luckily for you, has a
ROWNUM built-in variable that indicates the row number in the result set. It is very similar to the
ROW_NUMBER() function in T-SQL, except that the latter can only be used with partitioning. If you are still in T-SQL mode, think of partitioning by a primary key with auto-incremented identity (which returns the same dataset as the query above):
WITH FirstActiveUsers AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY UserId) AS RowNumber FROM Users WITH(NOLOCK) WHERE Active = 1 ORDER BY DateAdded ) SELECT * FROM FirstActiveUsers WHERE RowNumber <= 100
But all in all,
ROWNUM is just that – the row number. So, In PL/SQL this becomes as simple as
TOP in T-SQL:
SELECT * FROM Users WHERE Active = 1 AND ROWNUM <= 100 ORDER BY DateAdded
Hope this helped you out!