Hi,

I'm going to implement batching read, and want to ensure that I get all data if bookmark field isn't unique. so I have to implement WITH TIES algorithm.

But I cant run any of the listed approaches.

Most of these approaches should work for any version of Oracle, but don't work for NetSuite.

solution 1:

SELECT TOP 5 WITH TIES *
FROM [ACCOUNTS]
ORDER BY [DATE_LAST_MODIFIED] ASC;

syntax error. ( 'TOP N' - works, 'TOP N WITH TIES' - doesn't)

solution 2:

SELECT *
FROM [ACCOUNTS]
ORDER BY [DATE_LAST_MODIFIED] ASC;
FETCH FIRST 5 ROWS WITH TIES

syntax error.

solution 3:

SELECT *
FROM [ACCOUNTS]
WHERE [DATE_LAST_MODIFIED] <= (
        SELECT [DATE_LAST_MODIFIED]
        FROM [ACCOUNTS]
        WHERE rownum = 5
        ORDER BY DATE_LAST_MODIFIED ASC
    )