How to write case statement to display only 1 row in flag column for an ID with 2 different values-CodePudding

I have a query with multiple joins that is being used to extract data from multiple tables. As part of the select I have multiple case statements. I a stuck with one of them. I need to add one condition where I get only one row each for Chris and John with flag 'Y', if they have PCT as 100 and the '0' or 'NULL' PCT rows should not be displayed.

DeptID	Employee	PCT	Utlility_PCT
101	Chris	100	Y
101	Chris		N
101	Sam	0	N
101	John	100	Y
101	John		N

Currently my case statement is case when PCT = 100 then 'Y' else 'N' end Utility_PCT

I want my result set to look like:

DeptID	Employee	PCT	Utlility_PCT
101	Chris	100	Y
101	Sam	0	N
101	John	100	Y

CodePudding user response：

What you describe is filtering, and requires a WHERE clause. We can use ROW_NUMBER() to enumerate the rows according the priority rules that you describe, then use that information to filter out.

You are not showing your current query, so I'll assume it comes from a CTE:

with res as (... your query ...)
select *
from (
    select r.*,
        row_number() over(
            partition by deptid, employee
            order by case when pct = 100 and utility_pct = 'Y' then 0 else 1 end, pct, utility_pct
        ) rn
    from res r
) r
where rn = 1

The query limits the result to one row per department / employee tuple; when there is a row with pct = 100 and utility_pct = 'Y', it is selected, else we get the row with the smallest pct and the smallest utility_pct (this can be adapted by modifying the end of the order by clause of the window function).

CodePudding user response：

Since there is a tag 'greatest-n-per-group' you could filter the rows using GREATEST() function:

Select *
From   tbl t
Where  PCT = GREATEST( (Select PCT From 
                            (Select DEPT_ID, EMPLOYEE, Max(Nvl(PCT, -1)) "PCT" From tbl Group By DEPT_ID, EMPLOYEE) 
                        Where DEPT_ID = t.DEPT_ID And EMPLOYEE = t.EMPLOYEE)  )

With your sample data:

WITH
    tbl AS
        (
            Select 101 "DEPT_ID", 'Chris' "EMPLOYEE",  100 "PCT", 'Y' "UTILITY_PCT" From Dual Union All
            Select 101 "DEPT_ID", 'Chris' "EMPLOYEE", Null "PCT", 'N' "UTILITY_PCT" From Dual Union All
            Select 101 "DEPT_ID", 'Sam'   "EMPLOYEE",    0 "PCT", 'N' "UTILITY_PCT" From Dual Union All
            Select 101 "DEPT_ID", 'John'  "EMPLOYEE",  100 "PCT", 'Y' "UTILITY_PCT" From Dual Union All
            Select 101 "DEPT_ID", 'John'  "EMPLOYEE", Null "PCT", 'N' "UTILITY_PCT" From Dual 
        )

... the result is:

DEPT_ID	EMPLOYEE	PCT	UTILITY_PCT
101	Chris	100	Y
101	Sam	0	N
101	John	100	Y

If there could be more rows per group than in the sample you should probably do some additional filterings and/or groupings.
Regards...