[PostgreSQL] ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
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.
- S0
Sandeep Kumar Nov 07 2020
In PostgreSQL when you want distinct result from
SELECT
query, column appear inORDER BY
have to appear inDISTINCT
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.
- M-3
Marry Christ 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
, columnt.isnoindex
does not appear in theSELECT
clause.
You can removet.isnoindex
column fromORDER BY
or add more this column to theSELECT
query.
* Type maximum 2000 characters.
* All comments have to wait approved before display.
* Please polite comment and respect questions and answers of others.