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) A socialist is someone who will give you the shirt off *someone else's* back.
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