Question 413137 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to combine DISTINCT with CASE WHEN or something similar?

I have a situation where I need to use DISTINCT to get unique values. But there is additional condition. In the duplicate rows I get some have a value SUCCESS and some have value FAILURE. I need it so that in the DISTINCT result, if any of the duplicates has a FAILURE then the DISTINCT result will have a failure, else it will be SUCCESS. How can i achieve this ?

SELECT DISTINCT COL1, COL2, COL3, Result (
     Select COL1, COL2, COL3, CASE WHEN resolved THEN 'SUCCESS' ELSE 'FAILURE' END as Result FROM table
     UNION
     // Some other queries with similar results
)
Tags:

3

Answers


  1. 0

    You can achieve that with GROUP BY:

    SELECT COL1, COL2, COL3, MIN(Result)
FROM (<your table here>) t
GROUP BY COL1, COL2, COL3
    Login or Signup to reply.
  2. 0

    You can count ‘FAILURE’s directly.

    See example

    SELECT COL1, COL2, COL3
 ,case when sum(Result)>0 then 'FAILURE'
  else 'SUCCESS'
  end Result
FROM (
     Select COL1, COL2, COL3
       ,CASE WHEN resolved THEN 0 -- 'SUCCESS'
        ELSE 1 -- 'FAILURE'
        END as Result 
     FROM table
     UNION
     // Some other queries with similar results
)t
GROUP BY COL1, COL2, COL3
    Login or Signup to reply.
  3. 0

    You can use the distinct on clause and order the result according to the boolean value

    select distinct on (col1, col2, col3) col1, col2, col3, 
          CASE WHEN resolved THEN 'SUCCESS' ELSE 'FAILURE' END as Result
from test
order by col1, col2, col3, resolved;

    db fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search