In today’s continuation of the SQL / Oracle series, I thought it’d be nice to show how different the two are for retrieving the top number of rows.

SQL Server

There are a couple different ways to get the top rows in SQL Server. You can either do this by [n] or by [%]. You can choose to use an order by or not. If no ORDER BY clause is used the rows will be based on the tables default sort order.

SELECT TOP 10 AddressId FROM Person.Address

OR

SELECT TOP 1 PERCENT AddressId FROM Person.Address

Oracle

Top seems easy enough to use and you’d think it’s part of the ANSI standard for SQL but it’s not. TOP is not included in Oracle’s language; instead, there’s ROWNUM.

If you’re coming from SQL Server, ROWNUM doesn’t work you may expect. You can’t just replace TOP with ROWNUM. Another catch is that ROWNUM is assigned before the ORDER BY so you may want a derived table to get the top rows with a sort.

SELECT table_name FROM sys.all_tables WHERE rownum <= 25;

OR for a sorted list

SELECT table_name FROM (SELECT table_name FROM sys.all_tables ORDER BY table_name) WHERE rownum <= 25;

Conclusion

Simple isn’t always better. I like SQL Server’s TOP operator but it’s only going to give you the top number or percent. To get the next 5 or next [n] you’ll have to use OFFSET clause that was introduced in SQL Server 2012. This is called Pagination and is a very important feature for web pages or applications that want to paginate results.

Getting the next [n] in Oracle is a breeze with ROWNUM and has been around since probably the beginning of time before even the white walkers existed. To paginate with ROWNUM you simply use two derived tables like the following example:

SELECT table_name FROM (SELECT table_name, rownum rnum FROM (SELECT table_name FROM sys.all_tables ORDER BY table_name) WHERE rownum <= 20) WHERE rnum > 10;

You can find more of my posts at my blog: SQLTechBlog.com. If you like the information you found here, please follow me at LinkedIn, Twitter, and Instagram.