user75ponic user75ponic - 1 year ago 49
SQL Question

Conditional Rows Based On Column

In SQL how can I exclude a row based on a value of a column.

E.g. I have a column named as PROJECT_MGR_CODE where value is General Manager
If General Manager exists in the result set, then I would like to eliminate Director and CE which are above General Manager.

If General Manager doesn't exist in the result set, then it should bring Director and if Director doesn't exist, then it should bring CE

Answer Source

I'm assigning a hierarchy level to each row and then check for the highest level:

   SELECT dt.*,
      -- max level over all rows
      MAX(hierarchy_level) OVER () AS max_level
      SELECT T2.*,
            WHEN project_mgr_code LIKE 'CE%'              THEN 1
            WHEN project_mgr_code LIKE 'Director%'        THEN 2
            WHEN project_mgr_code LIKE 'General Manager%' THEN 3
            ELSE NULL  -- all other rows
         END AS hierarchy_level
      FROM T2
    ) dt
 ) dt
WHERE hierarchy_level >= max_level -- best match manager
   OR hierarchy_level IS NULL      -- all other rows 

This logic can easily be enhanced to add more levels.