E0015 distinct-order-by

SELECT DISTINCT ordered by a column it doesn't select.

What it reports

SELECT DISTINCT is ordered by a column that is not in its select list, so each distinct row could have several values to sort by. PostgreSQL and MySQL reject the query. In PostgreSQL, SELECT DISTINCT ON (...) must also start its ORDER BY with the DISTINCT ON expressions.

Example

SELECT DISTINCT user_id FROM orders ORDER BY total DESC;

error[E0015]: ORDER BY 'total' is not in the select list of SELECT DISTINCT
  --> q.sql:1:46
    |
  1 | SELECT DISTINCT user_id FROM orders ORDER BY total DESC;
    |                                              ^^^^^
    = help: With DISTINCT, ORDER BY can only use selected columns: select it too, or drop DISTINCT

Fixed:

SELECT user_id FROM orders GROUP BY user_id ORDER BY max(total) DESC;

Notes

Only plain column references in ORDER BY are checked; a column is considered selected when any select item or alias has its name. SQLite accepts such queries, so nothing is reported for it.

Configuring

# sqlsift.toml
[rules]
distinct-order-by = "warn"   # or "off"

Or for a single line: -- sqlsift:disable E0015. The examples use the schema on the Rules page.