I have a table as shown below
| emp_id | emp_name | flag | count |
|---|---|---|---|
| 1 | A | Y | 1 |
| 1 | A | N | 5 |
| 1 | B | N | 5 |
| 1 | C | N | 8 |
| 2 | X | N | 1 |
| 2 | X | Y | 5 |
| 2 | Y | N | 5 |
| 2 | Z | N | 2 |
I need to pick the rows when the first two columns are the same(duplicates), then you pick flag 'Y'. The output should look like
| emp_id | emp_name | flag | count |
|---|---|---|---|
| 1 | A | Y | 1 |
| 1 | B | N | 5 |
| 1 | C | N | 8 |
| 2 | X | Y | 5 |
| 2 | Y | N | 5 |
| 2 | Z | N | 2 |
Tried a sql inner join but gives only records with 'Y' flag
CodePudding user response：
You can use ROW_NUMBER() to find the rows you want, as in:
select emp_id, emp_name, flag, count
from (
select *,
row_number() over(partition by emp_id, emp_name
order by case when flag = 'Y' then 1 else 2 end) as rn
from t
) x
where rn = 1