CODEWITHSUNDEEP
sqlsql-servert-sql
PatPatPat
PatPatPat

Reputation: 31

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

Can you please suggest how I can write a query to retrieve the following?

I need to find any rows where the value in A 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

Should return 


2547    27 
2547    25

As 2547 has two non identical values in column B

Thanks in anticipation

P

Upvotes: 3

Views: 48

Answers (3)

void
void

Reputation: 7890

it's simple just use a nested select statement which one of them has count, group by and having 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

Upvotes: 1

Raj More
Raj More

Reputation: 48016

Try this

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)

Upvotes: 2

roman
roman

Reputation: 117380

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 fiddle demo

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 fiddle demo

Upvotes: 2

Related Questions