Oninit
Talk to an expert →

Informix Error -19828: ORDER BY column or expression must be in SELECT list in this context.

Cause and resolution

ORDER BY column or expression must be in SELECT list in this context.

You cannot omit the ORDER BY column or expression from the SELECT list in the following situations:

* The UNIQUE or DISTINCT operator is used. * The UNION, INTERSECT, or MINUS operator is used. * The query selects into a temporary table (SELECT ... INTO TEMP). * A distributed query references a server and requires ORDER BY columns to be in the SELECT list is used. * The query uses the GROUP BY clause with aggregate expressions. * The expression uses an alias to a column substring.

Revise the SQL statement to follow these rules.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-19828 fires when an ORDER BY column/expression is omitted from the SELECT list in a context that requires it — per the official guidance:

  1. UNIQUE/DISTINCT used, per the official guidance.
  2. UNION/INTERSECT/MINUS used, per the official guidance.
  3. The query selects into a temporary table (SELECT ... INTO TEMP), per the official guidance.
  4. A distributed query referencing a server requiring ORDER BY columns in the SELECT list, per the official guidance.
  5. GROUP BY used with aggregate expressions, per the official guidance.
  6. The expression uses an alias to a column substring, per the official guidance.
Solutions / Resolution
  1. Add the ORDER BY column/expression to the SELECT list, per the official guidance, in any of the listed contexts.
Examples
ORDER BY omitted from SELECT with DISTINCT
SELECT DISTINCT col1 FROM tab ORDER BY col2;
-- -19828: col2 must be in the SELECT list
Corrected
SELECT DISTINCT col1, col2 FROM tab ORDER BY col2;
Diagnostic Checks
  1. Check whether the query matches one of the six listed contexts, and add the missing ORDER BY column/expression to the SELECT list.
Related Errors / Related Topics

No closely related error codes are cross-referenced for -19828 in this set yet.

An ORDER BY column/expression is missing from the SELECT list in a context that requires it — add it to the SELECT list.