CODEWITHSUNDEEP
sqlselect
Arvind Singh
Arvind Singh

Reputation: 792

SQL query to fetch rows which are not same for a given group

For example for a table as below 

c1      c2      c3
a       b       1
a       b       2
c       d       1
c       d       1
e       f       3
e       f       3
e       f       3
x       y       5

In this scenario I want the group value of c1, c2 where all at least one of the c3 is not same. So in this case (a, b) as one row of (a, b) is 1 and another row of (a, b) is 2.

Also row (x, y) must not be reported.

Upvotes: 1

Views: 163

Answers (3)

Tobsey
Tobsey

Reputation: 3400

Edit: BAsed on single pairs not being included:

If you want to get each pair, C1,C2, for which every value of C3 is different, the following will do it:

SELECT
    C1,
    C2
FROM
    dbo.Table1
GROUP BY
    C1,
    C2
HAVING
    COUNT(1) = COUNT(DISTINCT C3)
AND COUNT(1) > 1

Upvotes: 1

John Woo
John Woo

Reputation: 263693

SELECT  c1, c2
FROM    tableName
GROUP   BY c1, c2
HAVING  COUNT(DISTINCT c3) > 1

OUTPUT

╔════╦════╗
║ C1 ║ C2 ║
╠════╬════╣
║ a  ║ b  ║
╚════╩════╝

but of you want to get all the rows with all its columns

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  c1, c2
            FROM    tableName
            GROUP   BY c1, c2
            HAVING  COUNT(DISTINCT c3) > 1
        ) b ON  a.c1 = b.c1 AND 
                a.c2 = b.c2

OUTPUT

╔════╦════╦════╗
║ C1 ║ C2 ║ C3 ║
╠════╬════╬════╣
║ a  ║ b  ║  1 ║
║ a  ║ b  ║  2 ║
╚════╩════╩════╝

Upvotes: 4

Tim Schmelter
Tim Schmelter

Reputation: 460048

Use EXISTS:

SELECT c1, c2, c3
FROM dbo.TableName t1
WHERE EXISTS
(
    SELECT 1 FROM dbo.TableName t2
    WHERE t1.c1 = t2.c1 AND t1.c2 = t2.c2
    AND   t1.c3 <> t2.c3
)

Demo (if you just want one record, use t1.c3 < t2.c3)

Upvotes: 1

Related Questions