CODEWITHSUNDEEP
sqlpostgresql
Shruti Rawat
Shruti Rawat

Reputation: 697

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

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

The below given query is giving me error:

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

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

Upvotes: 2

Views: 14701

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657202

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 BY expression part of the SELECT list:

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 may be redundant noise. Or your example may be a simplification of a more complex query, or you really want to GROUP BY / DISTINCT ON a few selected columns only. Here is a more useful related answer for the latter:

Basics for DISTINCT ON:

Upvotes: 5

Related Questions