Published on February 2, 2021.

One big advantage of using SuiteQL is that it makes it possible to extract large amounts of data from NetSuite in a single request. For example, when making SuiteQL calls via SuiteTalk REST, you can get up to 3,000 records per request. When making SuiteQL calls via RESTlets (with the N/query module), you can get up to 5,000 records per request.

However, getting beyond those limitations - and paginating the results to get additional results - can be challenging. And it's one of the most frequently asked questions that I get from other NetSuite developers.

Understanding ROWNUM

The key to paginating SuiteQL query results - and getting beyond some of SuiteQL's inherent limitations - is understanding and properly utilizing Oracle's ROWNUM pseudocolumn. Understanding that ROWNUM is a "pseudocolumn" - not a function - is a good first step.

A pseudocolumn is defined by Oracle as being something that "behaves like a table column, but is not actually stored in the table." When you're using ROWNUM, it often seems as though you're working with a function.

So what does ROWNUM do, exactly? It's essentially a column indicating the position ("row number") of a row in a query's result set. So for the first row, the value of ROWNUM will be 1. For the second row, it will be 2. And so on.

But there's more to it than that. It's when ROWNUM's value is applied to the results that is important. And if you don't keep that timing in mind, you can get yourself in trouble.

ROWNUM values are assigned to rows after a query's filters have been applied. In other words, ROWNUM is applied after the rows have been selected. Also, this happens before any aggregate functions are calculated, and before the rows have been sorted. As a result, using ROWNUM with a top-N type of query - or any query where the results are being sorted - can often yield unexpected results.

Let's take a look at a few queries - and I encourage you to try these yourself in your own NetSuite account.

SELECT
	ROWNUM AS RN,
	LastName,
	FirstName
FROM
	Employee

That query will return the employee records and the ROWNUM values (aliased as RN) will be what you'd expect. The first row will have an RN value of 1, the second will be 2, and so on.

However, if we sort the results, like this...

SELECT
	ROWNUM AS RN,
	LastName,
	FirstName
FROM
	Employee
ORDER BY
	LastName,
	FirstName

... then things start to go off the rails. The value of RN for the first row will not necessarily be 1. Instead, it will be based on whatever the value was before the rows were sorted. For example, for the first row, it might be 49. For the second row, it might be 45. And so on.

And again, this is because RN is calculated after the filters were applied (the rows were selected), but before they were sorted.

Paginating With ROWNUM

Ok, so then how, exactly, do we use ROWNUM for pagination?

The key is to use ROWNUM with subqueries. And specifically, not one, but two non-correlated subqueries.

Here's an example.

SELECT
	*
FROM
	(
		SELECT
			ROWNUM AS RN,
			*
		FROM
			(
				SELECT 
					LastName, 
					FirstName
				FROM 
					Employee
				WHERE
					LastName IS NOT NULL
				ORDER BY
					LastName, 
					FirstName 
			)
	)
WHERE
	( RN BETWEEN 10 AND 20 )

Notice that the original query is nested as a subquery within two other queries. The second query takes the results of the innermost query and adds the ROWNUM pseudocolumn. And the third, outermost query, uses the ROWNUM value to apply pagination filters. In this case, we're asking for rows 10 through 20. Of course, you could just as easily have asked for rows 100 through 200, rows less than 501, rows 5001 through 10000, and so on.

Note that if you were to apply the pagination filters directly to the second query, you would still very likely get unexpected results. That's because ROWNUM is applied after the rows are selected from the innermost query. In other words, the pagination filters must be applied via the outermost query.

Wrapping Up

That's how you can use ROWNUM to paginate the results of a SuiteQL query. It is, admittedly, a very odd looking query pattern. But it works.

Keep in mind that when using this technique, you will still be limited to retrieving 3,000 rows at a time when making calls via SuiteTalk REST, and 5,000 rows when using the N/query module. You'll have to take that into account when using this technique.

I hope you find all of this to be helpful, and that it helps you to unlock SuiteQL's capabilities. If you have any questions about this technique, or about SuiteQL in general, please feel free to reach out to me either via email or by posting a question to the NetSuite Professionals Slack Community.