Search code examples
sqlstringpostgresqlgreatest-n-per-group

Postgres Query to Combine multiple Rows into one based on Status Condition

I have a example of below data

Col1 Col2 Status
a    ab   Failed
a    bc   Running
a    cd   Completed

The output i'm expecting is

Col1 Status
a    Failed

i.e any one Subjob(Col2) is failed then the Main job should be displayed as Failed

Solution

  • One option uses distinct on and a conditional sort:

    select distinct on (col1) col1, status
from mytable
order by 
    col1,
    case status
       when 'Failed'    then 0
       when 'Running'   then 1
       when 'Completed' then 2
    end

    This gives priority to the "Failed" status, followed by "Running", then "Completed".

    You could also use booleans for ordering, like:

    order by 
    col1,
    (status = 'Failed')    desc,
    (status = 'Running')   desc,
    (status = 'Completed') desc