SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Duplicate Names
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 07/30/2013 :  10:00:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 07/30/2013 :  10:09:54  Show Profile  Reply with Quote
SELECT Client, COOID FROM 
(
	SELECT *,
		COUNT(*) OVER(PARTITION BY COID) AS N
	FROM
		JobOrder
)s WHERE N > 1;
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 07/30/2013 :  10:18:25  Show Profile  Reply with Quote
sorry, Distinct client has to be more than 1
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/30/2013 :  10:53:55  Show Profile  Reply with Quote
Can you show us what your expected output should be?
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 07/31/2013 :  06:44:29  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/31/2013 :  07:41:31  Show Profile  Reply with Quote
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
Go to Top of Page

SQLNoob81
Starting Member

South Africa
38 Posts

Posted - 07/31/2013 :  10:11:52  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 07/31/2013 :  10:19:59  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000