CODEWITHSUNDEEP
sqlpostgresql
Alex Herman
Alex Herman

Reputation: 2848

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

I'm new to SQL.

I guess I've misunderstood the concept of how to use DISTINCT keyword.

Here's my code:

SELECT DISTINCT(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 text, '|') FROM definitions WHERE entry_id = d.entry_id) AS definitions 
FROM entries AS 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.order_number

The error is as follows:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 6:  ORDER BY e.order_number

Just trying to understand what my SELECT statement should look like.

Upvotes: 2

Views: 16903

Answers (2)

Pancho
Pancho

Reputation: 2193

It appears to me that you are trying to distinct on a single column and not on others - which is bound to fail.

For example, select distinct a,b,c from x returns the unique combinations of a,b and c, not unique a but normal b and c

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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.

Upvotes: 2

Related Questions