-2

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

Itty Bit
  • 23
  • 4
  • 1
    Please tag correctly. SQL Server or Postgres? They're not the same. Also, please do not use images for sample data or code. Post them in text format so that it can be easily used. And show your code. That way we can help you figure out why you only get records with a 'Y' flag. – squillman Oct 01 '21 at 20:31
  • Also, please provide the data as formatted text. Not images. Say that, *for some bizarre reason*, we wanted to test our answer after we had written it, we'd need the data to test against. I for one, however, am not going to copy-type that data, when you could have just copy-pasted the text... – MatBailie Oct 01 '21 at 20:33
  • What happens when multiple rows exists for a given where flag is Y? Do you assume this is not possible? Seems you have a "first in group" situation generally. – SMor Oct 01 '21 at 20:37
  • 1
    Does this answer your question? [Selecting first row per group](https://stackoverflow.com/questions/10930347/selecting-first-row-per-group) – SMor Oct 01 '21 at 20:39
  • Thank you for your comments! I have fixed the question! Thanks for the answers too – Itty Bit Oct 01 '21 at 21:28

1 Answers1

1

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
The Impaler
  • 45,731
  • 9
  • 39
  • 76