How to display records in SQL Server that have same value in Column 2 but value in Column 1 should n-CodePudding

I have a below table like this -

Policy  Column1  Column2
A         4        100
B         4        100
C         3        100
D         3        100
E         2        100
F         5        100

The Output should be

Policy Column1 Column 2
E         2      100
F         5      100

Can someone please guide me.

CodePudding user response：

Select * from policies
where Column1 in (select Column1 
  from policies 
  group by Column1 
  having count(*)=1);

DBFiddle demo is here

CodePudding user response：

Assuming you're after rows where the value in column1 is unique (i.e. no other row has that same value in column 1)

select max(Policy) Policy -- if we're just getting 1 row, the max is the only value
, Column1
, max(Column2) Column2 -- as above
from myTable
group by Column1 -- group by column 1 then
having count(1) = 1 -- count how many rows are in that group; if it's unique we get 1

CodePudding user response：

You want to divide the data into chunk via partition by. Then you can determine which of them has only a single row.

with data as (
    select *, count(*) over (partition by Column1, Column2) as cnt
    from T
)
select Policy, Column1, Column2 from data where cnt = 1;

This kind of query was harder to write twenty years ago. If you're learning SQL I'd encourage you to get a handle on some of the foundational concepts before diving into table expressions and analytic functions.