The Equivalent of T-SQL’s SELECT TOP N in Oracle PL/SQL

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

The 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!

This entry was posted in Oracle PL/SQL and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *