Pg::Error: Select Distinct, Order by Expressions Must Appear in Select List

PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list

The ORDER BY clause can only be applied after the DISTINCT has been applied. Since only the fields in the SELECT statement are taken into consideration for the DISTINCT operations, those are the only fields may be used in the ORDER BY.

Logically, if you just want a distinct list of event_id values, what order they occur in should be irrelevant. If order does matter, then you should add the start_time to the SELECT list so that there is context for the order.

Also, these two SELECT clauses are NOT equivalent, so be careful:

SELECT DISTINCT(event_id, start_time) FROM ...

SELECT DISTINCT event_id, start_time FROM ...

The second is the form you want. The first will return a series of records with the data represented as a ROW construct (a single column with a tuple inside). The second will return normal columns of data output. It only works as expected in the single-column case where the ROW construct is reduced down since it is only a single column.

PostgreSQL - SELECT DISTINCT, ORDER BY expressions must appear in select list

If you want one row per distinct e.id, then you are looking for distinct on. It is very important that the order by be consistent with the distinct on keys:

SELECT DISTINCT ON (e.id), e.id, e.text, e.priority, CAST(e.order_number AS integer),
       s.name AS source, e.modified_time, e.creation_time,
       (SELECT string_agg(DISTINCT d2.text, '|') FROM definitions d2 WHERE d2.entry_id = d.entry_id) AS definitions 
FROM entries e LEFT JOIN
     definitions d
     ON d.entry_id = e.id INNER JOIN
     sources s
     ON e.source_id = s.id
WHERE vocabulary_id = 22
ORDER BY e.id, e.order_number;

Given the subquery, I suspect that there are better ways to write the query. If that is of interest, ask another question, provide sample data, desired results, and a description of the logic.

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list using order by with uniq

1. You need to select the columns first so you can order them later in .order
2. The result will still have duplicated records in spite of using .uniq or .distinct because the generated query is SELECT DISTINCT products.*, prices.finish_date, ... tries to find all the combination of products.*, prices.finish_date and the special column that has a unique value (in this case you only want the products.id to be unique)

DISTINCT ON is the solution but using it is a little bit tricky for postgres because of SELECT DISTINCT ON expressions must match initial ORDER BY expressions.

Please try:

sub_query = Product.joins(:prices)
  .select("DISTINCT ON (products.id) products.*, CASE WHEN (products.quantity >= products.min_quantity) AND (prices.finish_date IS NULL OR prices.finish_date >= now()) THEN 0 ELSE 1 END AS t, prices.finish_date AS date")

query = Product.from("(#{sub_query.to_sql}) as tmp").select("tmp.*").order("tmp.t, tmp.date ASC")

How to solve for SELECT DISTINCT, ORDER BY expressions must appear in select list error

According to your description, this is your query (in a more readable form)

SELECT DISTINCT c.*
FROM companies c INNER JOIN
     share_holders sh
     ON sh.company_id = c.id
WHERE sh.name LIKE '%name%' 
ORDER BY c.total_standard_score is null, c.total_standard_score desc, c.code asc;

I suspect the problem is the expression on c.total_standard_score. That shouldn't be a problem, but it might be.

Your underlying database might support NULLS LAST:

ORDER BY c.total_standard_score desc nulls last, c.code asc;

But a more efficient way to write the query is to use EXISTS:

SELECT c.*
FROM companies c 
WHERE EXISTS (SELECT 1
              FROM share_holders sh
              WHERE sh.company_id = c.id AND sh.name LIKE '%name%'
             ) 
ORDER BY c.total_standard_score is null, c.total_standard_score desc, c.code asc;

This should not have any parsing problems and be more efficient as well.

PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

You need to add widget_steps.name to list of selected columns:

SELECT  DISTINCT "widgets".*, "widget_steps.name" FROM "widgets" INNER JOIN "widget_steps" ON "widget_steps"."widget_id" = "widgets"."id" INNER JOIN "widget_steps" "active_steps_widgets" ON "active_steps_widgets"."id" = "widgets"."active_widget_step_id" WHERE "widgets"."account_id" = 1 AND "widgets"."completed_at" IS NULL AND (("widgets"."sequential" = 't' AND "widget_steps"."assigned_to" = 5 AND "widget_steps"."id" = "widgets"."active_widget_step_id" AND "widget_steps"."completed_at" IS NULL) OR ("widgets"."sequential" = 'f' AND "widget_steps"."assigned_to" = 5 AND "widget_steps"."completed_at" IS NULL)) ORDER BY sequential DESC, widget_steps.name ASC LIMIT 10 OFFSET 0

This should not change logic of your query and will work fine.

In Rails you may use select method to set list of selected columns:

Widget.select('"widgets".*, "widget_steps.name"')

Hope this would help.

---

Related Topics