Search code examples
netsuitesaved-searches

Can RANK or ROW_NUMBER be used in summarized Netsuite search results?

I apologize if this question is a duplicate. Most answers I see were not related specifically to Netsuite search format.

I need to rank a search result by sum of amount, grouped by item. I tried ROW_NUMBER() OVER (ORDER BY Amount) but it didn't work. Any advice? Is this even possible in summarized search results?

search results format photo

Any help would be greatly appreciated.

Solution

  • As you are aggregating the results (MIN, MAX, SUM etc) you also need to aggregate the expression in the ORDER BY clause, so that the results returned by the ROW_NUMBER() function match up with the summarized results of the rest of the saved search. All you would need to do is add a SUM() around {amount}:

    ROW_NUMBER() OVER (ORDER BY SUM({amount}))