Self Join : How to select a specific set of data

I have a simple table with data as

As we can see that row 1 & 2 contains exact same ColumnA, ColumnB & ColumnC. I want to form a simple select that can ignore the ‘N’ values where we already have a yes, so something like

I tried using self join but guess not using it right or likely not using the right thing.

Can someone drop a little suggestion here?

This is what I am trying

SELECT
*
FROM
    [Table] AS main 
    INNER JOIN [Table] AS sub ON
        sub.columna = main.columna
        and
        sub.columnb = main.columnb
        and
        sub.columnc = main.columnc
WHERE
    -- This is where I am struggling to add a condition:
    ((main.columnd = 'Y'
    AND
    sub.columnd = 'Y')
    OR
    (main.columnd = 'N'
    AND
    sub.columnd = 'Y'))

Answer

I suggest using the window function row_number() to accomplish this. So its partitioning by the columns you want to compare, and ordering by the preferred result

declare @Test table (Col1 varchar(2), Col2 varchar(2), Col3 varchar(2), Col4 varchar(2));

insert into @Test (Col1, Col2, Col3, Col4)
values
('AA','BA','CA','Y'),
('AA','BA','CA','N'),
('BB','CD','DA','N'),
('CC','GH','IJ','Y');

select Col1, Col2, Col3, Col4
from (
  select Col1, Col2, Col3, Col4
    , row_number() over (partition by Col1, Col2, Col3 order by Col4 desc) RowNum
  from @Test
) X
-- To understand how this works comment out the following line
where RowNum = 1;

Returns:

Col1    Col2    Col3    Col4
AA      BA      CA      Y
BB      CD      DA      N
CC      GH      IJ      Y

Note the data displayed as formatted text, and the DDL/DML statements to setup test data.