postgresql – select the highest-scoring columnname from left to right

One option uses greatest() and a case expression:

select t.*,
    case greatest(step_01, step_02, step_03)
        when step_01 then 'step_01'
        when step_02 then 'step_02'
        when step_03 then 'step_03'
    end as archive_status
from mytable t

While this would work for your current problem, I would recommend normalizing your design. Each step should be stored in a separate row rather than as a column, in a structure like (folder_name, step, status). Then you would use distinct on:

select distinct on (folder_name) t.*
from newtable t
order by folder_name, status desc, step

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top