Duplicate Names - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Duplicate Names

Author

Topic

SQLNoob81
Starting Member

38 Posts

Posted - 2013-07-30 : 10:00:34

Hi All

I have a Table called Joborder, Fields called Client(String), COID(Int)

I need to display * from Joborder where COID has more than 1 Client

Sounds simple but I cant get my head around it!

Thanks in Advance

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-30 : 10:09:54

[code]SELECT Client, COOID FROM
(
SELECT *,
COUNT(*) OVER(PARTITION BY COID) AS N
FROM
JobOrder
)s WHERE N > 1;[/code]

SQLNoob81
Starting Member

38 Posts

Posted - 2013-07-30 : 10:18:25

sorry, Distinct client has to be more than 1

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-30 : 10:53:55

Can you show us what your expected output should be?

SQLNoob81
Starting Member

38 Posts

Posted - 2013-07-31 : 06:44:29

ID Client COID
1 Company1 1001
2 Company2 1001
3 Company4 1004
4 Company5 1004
5 Company9 1007
6 Company10 1007

So it lists all that have more than 1 client name with the same COID.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 07:41:31

What was the problem with earlier given suggestion? I think it should give you what you're looking for.

or do you mean this?


SELECT t.*
FROM Table t
INNER JOIN (SELECT COID
            FROM Table
            GROUP BY COID
            HAVING COUNT(DISTINCT Client) > 1
           )t1
ON t1.COID = t.COID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

SQLNoob81
Starting Member

38 Posts

Posted - 2013-07-31 : 10:11:52

Still not working, this gives me results if client name appears more than 1, I only want results where the client name is different but has the same COID

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-31 : 10:19:59

quote:
Originally posted by SQLNoob81

Still not working, this gives me results if client name appears more than 1, I only want results where the client name is different but has the same COID

That is what the query Visakh posted will do. Can you post a set of sample data for which you are not getting the expected output and also the output you are expecting for that sample data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources