[PostgreSQL] ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Dung Do Tien Nov 07 2020 44

I have a DB in PostgreSQL. Now I want to get all the info data from Tag table and want to know the content of that tag. I write command select as below:

SELECT DISTINCT t.id, t.keyword as title, t.status,
(CASE 
 WHEN a.type = 1 THEN 'News' 
 WHEN a.type = 2 THEN 'Compare'
 WHEN a.type = 3 THEN 'Tips'
 WHEN a.type = 4 THEN 'New Car'
 WHEN a.type = 5 THEN 'Gallery'
 WHEN a.type = 6 THEN 'Stories'
 ELSE 'Other'
 END
) AS content_type
FROM tags t
LEFT JOIN articletagmapping atm1 ON atm1.tagid = t.id
LEFT JOIN articles a ON a.id = atm1.articleid
ORDER BY t.id DESC, t.isnoindex DESC

But I got an error ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ... a on a.id = atm1.articleid Order by status desc, countartic...
                                                             ^
(execution time: 63 ms; total time: 485 ms)

Please explain to me if you know any reason.

Have 2 answer(s) found.
  • In PostgreSQL when you want distinct result from SELECT query, column appear in ORDER BY have to appear in DISTINCT command.

    You can change your query as below:

    SELECT DISTINCT t.id, t.keyword as title, t.status, t.isnoindex,
    (CASE 
     WHEN a.type = 1 THEN 'News' 
     WHEN a.type = 2 THEN 'Compare'
     WHEN a.type = 3 THEN 'Tips'
     WHEN a.type = 4 THEN 'New Car'
     WHEN a.type = 5 THEN 'Gallery'
     WHEN a.type = 6 THEN 'Stories'
     ELSE 'Other'
     END
    ) AS content_type
    FROM tags t
    LEFT JOIN articletagmapping atm1 ON atm1.tagid = t.id
    LEFT JOIN articles a ON a.id = atm1.articleid
    ORDER BY t.id DESC, t.isnoindex DESC 

    I hope this answer is helpful for you.

    Sandeep Kumar Nov 07 2020
  • I think the root cause of the error is that your are ordering using ORDER BY t.id DESC, t.isnoindex DESC, column t.isnoindex does not appear in the SELECT clause. 
    You can remove t.isnoindex column from ORDER BY or add more this column to the SELECT query.

    Marry Christ Nov 07 2020
Leave An Answer
* NOTE: You need Login before leave an answer

* Type maximum 2000 characters.

* All comments have to wait approved before display.

* Please polite comment and respect questions and answers of others.

Popular Tips