Highlight Duplicate Values in a NetSuite Saved Search

I am looking for a way to highlight duplicates in a NetSuite saved search. The duplicates are in a column called "ACCOUNT" populated with text values.

NetSuite permits adding fields (columns) to the search using a stripped down version of SQL Server. It also permits conditional highlighting of entire rows using the same code. However I don't see an obvious way to compare values between rows of data.

Although duplicates can be grouped together in a summary report and identified by a count of 2 or more, I want to show duplicate lines separately and highlight each.

The closest thing I found was a clever formula that calculates a running total here:

sum/* comment */({amount})
 OVER(PARTITION BY {name}
 ORDER BY {internalid}
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

I wonder if it's possible to sort results by the field being checked for duplicates and adapt this code to identify changes in the "ACCOUNT" field between a row and the previous row.

Any ideas? Thanks!

Solution

This post has been edited. I have left the progression as a learning experience about NetSuite.

Original - plain SQL way - not suitable for NetSuite

Does something like this meet your needs? The test data assumes looking for duplicates on id1 and id2. Note: This does not work in NetSuite as it supports limited SQL functions. See comments for links.

declare @table table (id1 int, id2 int, value int);

insert @table values
(1,1,11),
(1,2,12),
(1,3,13),
(2,1,21),
(2,2,22),
(2,3,23),
(1,3,1313);
--select * from @table order by id1, id2;

select  t.*,
        case when dups.id1 is not null then 1 else 0 end is_dup     --identify dups when there is a matching dup record
from    @table t
left    join (  --subquery to find duplicates
        select  id1, id2
        from    @table
        group   by id1, id2
        having count(1) > 1
        ) dups
        on  dups.id1 = t.id1
        and dups.id2 = t.id2
order   by t.id1, t.id2;

First Edit - NetSuite target but in SQL.

This was a SQL test based on the example available syntax provided in the question since I do not have NetSuite to test against. This will give you a value greater than 1 on each duplicate row using a similar syntax. Note: This will give the appropriate answer but not in NetSuite.

select  t.*,
        sum(1) over (partition by id1, id2)
from    @table t
order   by t.id1, t.id2;

Second Edit - Working NetSuite version

After some back and forth here is a version that works in NetSuite:

sum/* comment */(1) OVER(PARTITION BY {name})

This will also give a value greater than 1 on any row that is a duplicate.

Explanation

This works by summing the value 1 on each row included in the partition. The partition column(s) should be what you consider a duplicate. If only one column makes a duplicate (e.g. user ID) then use as above. If multiple columns make a duplicate (e.g. first name, last name, city) then use a comma-separated list in the partition. SQL will basically group the rows by the partition and add up the 1s in the sum/* comment */(1). The example provided in the question sums an actual column. By summing 1 instead we will get the value 1 when there is only 1 ID in the partition. Anything higher is a duplicate. I guess you could call this field duplicate count.