Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
                @GordonLinoff I need to fetch entries records and avoid duplicates, Plus I would like to order them by order_number.
– Alex Herman
                Jul 9, 2018 at 19:35
                To someone who is reading this now, look at the error message, it clearly says something isn't present in select query  list but has been trying to use in order by query . In this query order_number doesn't appear in select  but appears in order by which causes the error to happen.
– Badri Paudel
                Mar 14, 2022 at 7:07
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
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.
                thank you!  DISTINCT ON (e.id), - comma is not needed. also, ORDER BY e.id, e.order_number; orders the collection by id instead of by order_number - swapping them doesn't help for some reason. I'll keep looking into this.
– Alex Herman
                Jul 9, 2018 at 19:45
                Have just resolved it actually: SELECT DISTINCT ON (e.order_number) CAST(e.order_number AS integer), e.text, e.priority, e.id, ... ... ORDER BY e.order_number, e.id;  Accepting your answer, great tip, again thanks!
– Alex Herman
                Jul 9, 2018 at 19:48
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.