NetSuite Administrators often need a quick way to review recent Customer activity, especially when working with Sales Orders. While Saved Searches are useful for many reporting needs, SuiteQL gives admins more flexibility when they need to rank, filter, and return a specific number of records per group.
A common example is finding the latest 3 Sales Orders for each Customer. Instead of manually checking each customer record or creating multiple filters, you can use the SuiteQL ROW_NUMBER() function to number each Customer’s Sales Orders from newest to oldest. Then, by filtering that ranking, you can return only the most recent orders for every customer in a single query.
In this example, “latest” is based on the Sales Order transaction date, with the transaction internal ID used as a tie-breaker when multiple orders have the same date.
SuiteQL Query
SELECT
ranked.entity, -- Customer internal ID
ranked.customer_name, -- Customer display name
ranked.tranid, -- Sales Order number
ranked.trandate, -- Transaction date
ranked.status, -- Sales Order status
ranked.foreign_total, -- Transaction total
ranked.row_num -- Ranking number per customer
FROM (
SELECT
t.entity, -- Customer internal ID
BUILTIN.DF(t.entity) AS customer_name, -- Converts customer ID to customer name
t.tranid, -- Transaction number
t.trandate, -- Transaction date
BUILTIN.DF(t.status) AS status, -- Converts status ID/code to display value
t.foreigntotal AS foreign_total, -- Transaction total amount
-- Number each Sales Order per customer, newest first
ROW_NUMBER() OVER (
PARTITION BY t.entity -- Restart numbering for each customer
ORDER BY t.trandate DESC, t.id DESC -- Newest transaction gets row_num = 1
) AS row_num
FROM transaction t
WHERE t.type = 'SalesOrd' -- Only Sales Orders
AND t.entity IS NOT NULL -- Exclude transactions without customer/entity
) ranked
WHERE ranked.row_num <= 3 -- Keep only latest 3 per customer
ORDER BY
ranked.customer_name,
ranked.row_num
- The inner query selects Sales Orders from the transaction table and assigns a row number to each order using ROW_NUMBER().
- PARTITION BY t.entity - This restarts the numbering for each customer.
- ORDER BY t.trandate DESC, t.id DESC - This sorts each Customer’s Sales Orders from newest to oldest based on transaction date. The Transaction internal ID is included as a secondary sort so results remain consistent when more than one Sales Order has the same transaction date.
- WHERE ranked.row_num <= 3 - The outer query then filters the ranked results. This keeps only rows 1, 2, and 3 for each customer.
To easily execute a SuiteQL query, see NetSuite Admin Tip | Running SUITEQL Using a Suitelet
DISCLAIMER: The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
We’d love to hear how you integrate SuiteQL into your NetSuite processes! Share your insights and experiences in the NetSuite Admin Corner.