How do you find the next-higher sequence value, after a known reference value, that also satisfies some other filter condition, in a single SQL query, without using ORDER BY combined with a row-limiting clause?

All FAQ questions / Reporting and analytics

Answer

Rather than trying to express this as one complex nested subquery with some kind of 'next higher value' pseudo-operator, which doesn't exist in standard SQL, simply combine a plain greater-than comparison with MIN() and your other filter conditions in one straightforward query: SELECT MIN(seq) FROM <table> WHERE seq > <reference_value> AND <other_condition>. MIN() applied over the already-filtered greater-than set directly returns the next qualifying higher value, without needing any special ranking or window-function syntax.

Documentation-based answer. Confirm the product and release before applying it. This answer has not been verified in an IFS environment.

← Browse Reporting and analytics questions