September 6, 2013 at 3:15 pm
Hello,
I have 1 table with some fields in it. Two of the fields are important to me.
I need to check if for each value in field 1 I have more than 1 value in field 2...
For example:
Customer Contract
1 (Peter) A
2 (John) N
2 (John) P
3 (Lisa) S
So basically how can I identify that for customer John I have 2 contracts N and P?
Thanks in advance!
September 6, 2013 at 3:22 pm
SELECT *
FROM dbo.tablename
WHERE Customer IN (
SELECT Customer
FROM dbo.tablename
GROUP BY Customer
HAVING COUNT(DISTINCT Contract) > 1
)
ORDER BY
Customer, Contract
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 6, 2013 at 3:36 pm
Thanks a lot!!!
September 10, 2013 at 4:06 am
HI ,
select * INTO Test FROM
(
select ROW_NUMBER()over(partition by customer order by customer) as counts ,customer,Contract
from tablename
) A where counts > 1
Select * from tablename where customer IN(SELECT customer from Test )
Hope it helps
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy