query - 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

General SQL Server Forums

New to SQL Server Programming

query

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2012-05-24 : 18:31:01

for the table,

orderid name workid
100 A 1001
100 A 1002
101 A 1003
101 B 1004
101 A 1005
101 B 1006

I want the output which has got more than one entry of same orderid with same name
In the above case, the output should be 100. 101 has more than one entry of orderid with same name but it has also got another entry with different name.
output should only be 100

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-24 : 18:46:29

Here's what I came up with:


create table #t (orderid int, name char(1), workid int)
insert into #t values(100,'A', 1001)
insert into #t values(100,'A', 1002)
insert into #t values(101,'A', 1003)
insert into #t values(101,'B', 1004)
insert into #t values(101,'A', 1005)
insert into #t values(101,'B', 1006)

select orderid, count(*) as NameCount
from
(
	select orderid, name
	from #t
	group by orderid, name
	having count(*) > 1
) t
group by orderid
having count(*) = 1

drop table #t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 09:59:38

[code]
select t.*
from #t t
inner join
(
select orderid
from #t
group by orderid
having count(distinct name)=1
and count(*) > 1
)t1
on t1.orderid = t.orderid
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-05-25 : 12:24:47

quote:

Originally posted by visakh16


select t.*
from #t t
inner join
(
select orderid
from #t
group by orderid
having count(distinct name)=1
and count(*) > 1
)t1
on t1.orderid = t.orderid

I didn't look at the execution plan, but wouldn't yours be less efficient since it's hitting the table twice?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources