Clever Geek Handbook

SQL Server 2008 Find a row based on 1 unique column and duplicates in the second column

Can you suggest how I can write a request to get the following?

I need to find any lines where the value in is duplicated, but the value in B is different


  A    B 
4567    2 
852125  9
444     8
2547    25
255     4
256     1
2547    25
2547    27
259     4
2547    25

Must come back


2547    27 
2547    25

Since 2547 has two not the same values ​​in column B

Thanks in anticipation

R

+3


source to share


3 answers 


with cte1 as (
    select distinct A, B from Table1
), cte2 as (
    select A, B, count(*) over(partition by A) as cnt from cte1
)
select
    A, B
from cte2 
where cnt > 1

sql

or



with cte as (
    select distinct
        A, B, min(B) over(partition by A) as m1, max(B) over(partition by A) as m2
    from Table1
)
select
    A, B
from cte
where m1 <> m2

sql

+2


source


try it

With DemoTable  AS
(
          Select 4567   A,2  B
Union All Select 852125  ,9
Union All Select 444     ,8
Union All Select 2547    ,25
Union All Select 255     ,4
Union All Select 256     ,1
Union All Select 2547    ,25
Union All Select 2547    ,27
Union All Select 259     ,4
Union All Select 2547    ,25
)
Select Distinct A, B
From DemoTable  
Where A In
(
    Select A
    From DemoTable
    Group By A
    Having Count (Distinct B) > 1
)


Output

A           B
----------- -----------
2547        25
2547        27

(2 row(s) affected)
+2


source


just just use a nested select statement in which one has count , group and s statements.

select distinct tbl.A,tbl.B from table_name tbl
 where tbl.A in (select A from (
  select  tb.A, count(tb.B) 
  from table_name tb group by tb.A having count(tb.B)>1)) 
order by tbl.A
+1


source






More articles:


All Articles