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
I'm assigning a hierarchy level to each row and then check for the highest level:
SELECT * FROM ( SELECT dt.*, -- max level over all rows MAX(hierarchy_level) OVER () AS max_level FROM ( SELECT T2.*, CASE 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.