Informix Error -25853: The ntile, lead, lag and ranking window functions require window order.
Cause and resolution
The ntile, lead, lag and ranking window functions require window order.
The aggregate RANK(), DENSE_RANK(), DENSERANK(), PERCENT_RANK(), LAG(), LEAD(), CUME_DIST(), and NTILE() window functions require the window-order clause.
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-25853 fires when RANK(), DENSE_RANK(), DENSERANK(), PERCENT_RANK(), LAG(),
LEAD(), CUME_DIST(), or NTILE() is called without a window-order clause — per the
official guidance, these functions require one.
- One of these window functions called without a window-order clause, per the official guidance — the direct, only cause.
Solutions / Resolution
- Add a window-order clause (
ORDER BY) to the window definition, per the official guidance.
Examples
RANK without a window-order clause
SELECT customer_id, RANK() OVER (PARTITION BY region) FROM orders;
-- -25853: missing ORDER BY in the window definition
Corrected
SELECT customer_id, RANK() OVER (PARTITION BY region ORDER BY total DESC) FROM orders;
Diagnostic Checks
- Check every
RANK/DENSE_RANK/DENSERANK/PERCENT_RANK/LAG/LEAD/CUME_DIST/NTILEcall for a window-order clause, and add one.
Related Errors / Related Topics
- -25854 — "The ntile, lead, lag, ranking and row numbering window functions cannot have a window-frame extent." A related restriction on the same function family, on frame extents rather than ordering.
A ranking/LAG/LEAD/NTILE-family window function was called without ORDER BY — add
a window-order clause.