CODEWITHSUNDEEP
sqlsql-server
Antoine Pelletier
Antoine Pelletier

Reputation: 3316

SQL select row where (this column has many different values in the other)

I want to select rows where some precise column has different values while another precise column has the same value.

Exemple :

COLUMN_A  |  COLUMN_B
__________|___________
          |
1         |   2002
1         |   2002
2         |   2001
2         |   2007
3         |   2010
3         |   2010

Now, suppose I want to know which Rows has the same A but different B, the query would return the rows

2         |   2001
2         |   2007

or just 

2

as long as I know which one is it ...

Upvotes: 0

Views: 139

Answers (3)

Y.B.
Y.B.

Reputation: 3586

This is the case for Count(Distinct ColumnName). It ensures that only unique values are taken into account.

With Src As (
    Select *
    From (Values
        (1, 2002),
        (1, 2002),
        (2, 2001),
        (2, 2007),
        (3, 2010),
        (3, 2010)
    ) V (COLUMN_A, COLUMN_B)
)
Select *
From Src
Where COLUMN_A In (
    Select COLUMN_A
    From Src
    Group By COLUMN_A
    Having Count(Distinct COLUMN_B) > 1 --<- "More than one unique value" condition
    )

COLUMN_A    COLUMN_B
       2        2001
       2        2007

Upvotes: 3

John Fowler
John Fowler

Reputation: 3281

This one is without GROUPing:

SELECT x.column_a, x.column_b, y.column_b
  FROM table_name x
  JOIN table_name y
    ON ( x.column_a = y.column_a AND x.column_b <> y.column_b )

You just join the table to itself, and provide the conditions you are looking for.

Upvotes: 2

Lamak
Lamak

Reputation: 70658

You can use:

SELECT COLUMN_A  
FROM dbo.YourTable
GROUP BY COLUMN_A  
HAVING MIN(COLUMN_B) <> MAX(COLUMN_B);

Another way can be using EXISTS:

SELECT *
FROM dbo.YourTable A
WHERE EXISTS(SELECT 1 FROM dbo.YourTable
             WHERE COLUMN_A = A.COLUMN_A
             AND COLUMN_B <> A.COLUMN_B);

Upvotes: 3

Related Questions