postgresql 错误：对于 SELECT DISTINCT，ORDER BY 表达式必须出现在选择列表中

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

提问by Shruti Rawat

I have a table Vulnerabilities from which I want to query records ordered by severity (column). Severity column holds value "High", "Medium","low".

我有一个表 Vulnerabilities，我想从中查询按严重性（列）排序的记录。严重性列包含值“高”、“中”、“低”。

The below given query is giving me error:

下面给出的查询给了我错误：

for SELECT DISTINCT, ORDER BY expressions must appear in select list"

对于 SELECT DISTINCT，ORDER BY 表达式必须出现在选择列表中”

SELECT DISTINCT vuln 
FROM Vulnerabilities vuln 
WHERE (lower(vuln.dsc) LIKE '%tomcat%') 
ORDER BY CASE vuln.severity 
            WHEN 'High' THEN 1 
            WHEN 'Medium' THEN 2 
            WHEN 'Low' THEN 3 
         END ASC

回答by Erwin Brandstetter

Use a subquery:

使用子查询：

SELECT *
FROM  (
   SELECT DISTINCT *
   FROM   Vulnerabilities vuln 
   WHERE  lower(dsc) LIKE '%tomcat%'
   ) sub
ORDER  BY CASE severity 
            WHEN 'High'   THEN 1 
            WHEN 'Medium' THEN 2 
            WHEN 'Low'    THEN 3 
          END;

Or make the ORDER BYexpression part of the SELECTlist:

或者将ORDER BY表达式作为SELECT列表的一部分：

SELECT DISTINCT
       CASE severity 
           WHEN 'High'   THEN 1 
           WHEN 'Medium' THEN 2 
           WHEN 'Low'    THEN 3 
       END AS severity_order, *
FROM   Vulnerabilities vuln 
WHERE  lower(dsc) LIKE '%tomcat%'
ORDER  BY 1;

But chances are, you don't want DISTINCT *at all. Like @a_horse commented, this only makes sense for complete duplicates, which is a rare case and only possible if you don't have defined any unique columns (like a PK!)

但很有可能，你根本不想要DISTINCT *。就像@a_horse 评论的那样，这只对完全重复才有意义，这是一种罕见的情况，只有在您没有定义任何唯一的列（如 PK！）时才有可能

DISTINCTmay be redundant noise. Or your example may be a simplification of a more complex query, or you really want to GROUP BY/ DISTINCT ONa few selected columns only. Here is a more useful related answer for the latter:

DISTINCT可能是多余的噪音。或者你的例子可能是一个更复杂的查询的简化，或者你真的只想GROUP BY/DISTINCT ON几个选定的列。这是后者更有用的相关答案：

• How to order distinct tuples in a PostgreSQL query

• 如何在 PostgreSQL 查询中对不同的元组进行排序

Basics for DISTINCT ON:

基础知识DISTINCT ON：

• Select first row in each GROUP BY group?

• 选择每个 GROUP BY 组中的第一行？