We’ve previously encountered issues using LIMIT/OFFSET in SuiteQL — specifically, empty result sets when querying around offset 18000, despite records existing in that range. We suspect this is due to JOIN filtering or unstable pagination caused by missing ORDER BY. We’re now using ROW_NUMBER() with an explicit ORDER BY item.id, Location.id to stabilize pagination. Can you confirm if the following query is safe to use for retrieving rows 18001 to 19000, or if there’s still a risk of missing/incomplete results due to the joins or dataset size? SELECT * FROM ( SELECT Location.id AS Location, TO_CHAR( CASE WHEN TRUNC(SYSDATE) < TO_DATE(EXTRACT(YEAR FROM SYSDATE) || '-04-01', 'YYYY-MM-DD') THEN EXTRACT(YEAR FROM SYSDATE) - 1 ELSE EXTRACT(YEAR FROM SYSDATE) END + 1 ) || 'D' || LPAD( TRUNC(SYSDATE) - TO_DATE( CASE WHEN TRUNC(SYSDATE) < TO_DATE(EXTRACT(YEAR FROM SYSDATE) || '-04-01', 'YYYY-MM-DD') THEN (EXTRACT(YEAR FROM SYSDATE) - 1) || '-04-01' ELSE EXTRACT(YEAR FROM SYSDATE) || '-04-01' END, 'YYYY-MM-DD' ) + 1, 3, '0' ) AS pxtime, Location.fullname AS Locationname, Location.subsidiary, item.id, item.fullname, item.itemid, item.displayname, item.description, itemunit.name AS itemunitname, item.purchasedescription, item.vendorname, item.cost, item.costingmethoddisplay, item.isdropshipitem, item.isspecialorderitem, item.weight, item.weightunit, item.class, item.createddate, item.lastmodifieddate, ia.fullname AS incomeaccount, aa.fullname AS assetaccount, ea.fullname AS expenseaccount, NVL(AggregateItemLocation.quantityonhand, 0) AS quantityonhand, NVL(AggregateItemLocation.quantityavailable, 0) AS quantityavailable, NVL(AggregateItemLocation.quantityonorder, 0) AS quantityonorder, NVL(AggregateItemLocation.quantitycommitted, 0) AS quantitycommitted, NVL(AggregateItemLocation.quantitybackordered, 0) AS quantitybackordered, NVL(AggregateItemLocation.reorderpoint, 0) AS reorderpoint, NVL(AggregateItemLocation.preferredstocklevel, 0) AS preferredstocklevel, NVL(AggregateItemLocation.qtyintransitexternal, 0) AS qtyintransitexternal, ROW_NUMBER() OVER (ORDER BY item.id, Location.id) AS row_num FROM item JOIN AggregateItemLocation ON item.id = AggregateItemLocation.item JOIN Location ON AggregateItemLocation.Location = Location.id LEFT JOIN account ia ON item.incomeaccount = ia.id LEFT JOIN account aa ON item.assetaccount = aa.id LEFT JOIN account ea ON item.expenseaccount = ea.id LEFT JOIN itemunit ON item.weightunit = itemunit.key WHERE item.isinactive = 'F' ) WHERE row_num > 18000 AND row_num <= 19000; Thanks! Let me know if this is the recommended approach.

AI generated answer (using internal Slack threads):

Based on the conversations provided, it seems that the query you've provided should work as expected. The use of

ROW_NUMBER()

with an explicit

ORDER BY

clause is a common and recommended approach to handle pagination in SQL, and it should work in SuiteQL as well. However, please note that the effectiveness of this approach can depend on the specific data and database schema. If there are duplicate values in the columns specified in the

ORDER BY

clause, the order of rows with duplicate values is not guaranteed, which could potentially lead to inconsistent pagination results. In your case, you're ordering by

item.id

and

Location.id

, so if there are multiple rows with the same

item.id

and

Location.id

, their order is not guaranteed. If this could be an issue, you might want to include additional columns in the

ORDER BY

clause to ensure a unique order. Also, please ensure that the joins in your query are correct and that they don't exclude any rows that should be included in the result set. Finally, please note that while this approach should work in theory, it's always a good idea to test it thoroughly with your specific data to ensure it works as expected. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******