Oninit
Talk to an expert →

Informix Error -309

-309 ORDER BY column or expression must be in SELECT list.

An expression or column name is in the ORDER BY clause of this SELECT statement, but the expression or column name is not in the select list (the list of values that follows the word SELECT). This action is not supported when a UNIQUE or DISTINCT operator is being used in a query.

When a UNIQUE or DISTINCT operator is being used in a query, all sort keys must be present in the output rows to determine the true order. Revise the statement to follow this rule.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-309 is specific to SELECT DISTINCT/UNIQUE queries: when either operator is used, every column or expression named in ORDER BY must also appear in the SELECT list — because duplicate elimination collapses rows before sorting, and the sort has nothing to work with for a column that isn't part of the (deduplicated) output.

  1. A SELECT DISTINCT/UNIQUE query with an ORDER BY referencing a column not in the select list — the direct cause.
  2. Sorting by a raw column while selecting only a computed/aggregated version of it — the select list has the expression, but ORDER BY names the underlying bare column (or vice versa), and they don't match closely enough for the server to line them up.
  3. A query written without DISTINCT originally, where ORDER BY could reference any column freely, later modified to add DISTINCT without revisiting the ORDER BY clause.
Solutions / Resolution
  1. Add every ORDER BY column or expression to the SELECT list, per the official guidance, when DISTINCT/UNIQUE is in use.
  2. If the sort key shouldn't be visible in the output, restructure the query to avoid DISTINCT (e.g. use GROUP BY instead if de-duplication by a subset of columns is the actual goal), or accept it as an extra output column.
  3. When adding DISTINCT to an existing query, review its ORDER BY clause for columns that will now need to be added to the select list.
Examples
ORDER BY column missing from a DISTINCT select list
SELECT DISTINCT customer_id, name FROM customers
ORDER BY signup_date;
-- -309: signup_date isn't in the SELECT list

Fix — add the sort column to the select list:

SELECT DISTINCT customer_id, name, signup_date FROM customers
ORDER BY signup_date;
DISTINCT added later without revisiting ORDER BY
-- Originally, without DISTINCT, this was fine:
SELECT customer_id, name FROM customers ORDER BY region;

-- After adding DISTINCT:
SELECT DISTINCT customer_id, name FROM customers ORDER BY region;
-- -309: region needs to be added to the select list now

Fix:

SELECT DISTINCT customer_id, name, region FROM customers ORDER BY region;
Diagnostic Checks
  1. Check whether the query uses DISTINCT/UNIQUE — this restriction only applies in that case.
  2. Compare every ORDER BY column/expression against the SELECT list for an exact or sufficiently matching entry.
Related Errors / Related Topics
  • -251 — "ORDER BY or GROUP BY column number is too big." Another ORDER BY-clause restriction, though about positional numbering rather than list membership.
  • -294 — "The column column-name must be in the GROUP BY list." The closest conceptual sibling — a comparable "referenced column must be in the [output-shaping] list" rule, applied to GROUP BY instead of DISTINCT/ORDER BY.

This restriction only bites when DISTINCT/UNIQUE is used — add the sort key to the select list, or drop DISTINCT if the sort key genuinely shouldn't appear in the output.