SQL to return selected rows

Question

SQL to return selected rows

Express12

SSCoach

Points: 18514
More actions
July 5, 2006 at 1:15 pm

#173148

Need SQL to return all rows where C1 occurs more than once, C1 is duplicated, AND, C2 contains different values.
In this example, the AAA rows and CCC rows would be rendered; they meet the above criteria:
C1    C2
AAA   123
AAA   456
BBB   333
BBB   333
CCC   788
CCC   788
CCC   891
DDD   555
BT

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76001 More actions · Answer 1

SELECT C1,C2

FROM dbo.YourTable AS YT

WHERE C1 IN (
    SELECT C1 
    FROM dbo.YourTable 
    GROUP BY C1 
    HAVING COUNT(*)>1 
       AND MIN(C2)MAX(C2)
)

LinkedIn Profile

stax68 SSChampion Points: 11711 More actions · Answer 2

You may need to add some null handling in the having clause.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant