Selecting items that don't appear more than once

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 2008 Forums

Transact-SQL (2008)

Selecting items that don't appear more than once

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2012-04-10 : 11:57:05

Apologies in advance for stupid question. Here's some data:


intSeriesId intBrandId
59          159728
59          159729
62          159729
188         159729
215         159719

I need to select all the seriesIds from this list which have one and only one intBrandId. So in the above example 59 is invalid and I don't want it, and all the others are valid.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-10 : 12:05:31

please post the final result you would like to see. also use this method to ask question and get a quicker response. otherwise people have to write more script that you could have and they might not respond to your question


declare @mattt table(intSeriesId int, intBrandId int)
insert into @mattt
select 59,          159728
UNION
SELECT 59,          159729
UNION
SELECT 62,          159729
UNION
SELECT 188,         159729
UNION
SELECT 215,         159719

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-10 : 12:07:55

[code]
declare @mattt table(intSeriesId int, intBrandId int)
insert into @mattt
select 59, 159728
UNION
SELECT 59, 159729
UNION
SELECT 62, 159729
UNION
SELECT 188, 159729
UNION
SELECT 215, 159719

;with cteMatt(intSeriesId, intBrandId, nRow)
as
(
select intSeriesId,
intBrandId,
ROW_NUMBER() over(partition by intSeriesId order by intSeriesId DESC) nRow

from @mattt
)
select * from cteMatt where nRow = 1
[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

mattt
Posting Yak Master

194 Posts

Posted - 2012-04-10 : 12:11:27

Thanks for offering to assist. I'll remember to post data in query format in the future to save time.

I haven't stated my requirement clearly (as indeed you pointed out). What I'm expecting to see from that data set is this:


intSeriesId  intBrandId
62           159729
188          159729
215          159719

So I don't want to select the series that has more than one value in the intBrandId column.

I appreciate you trying to help but your code selects one of the intBrandIds for intSeriesId 59 - I don't want it all. I hope that's clearer.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-10 : 12:23:11

one of many ways you could do it


declare @mattt table(intSeriesId int, intBrandId int)
insert into @mattt
select 59,          159728
UNION
SELECT 59,          159729
UNION
SELECT 62,          159729
UNION
SELECT 188,         159729
UNION
SELECT 215,         159719

;with cteMatt(intSeriesId, Dups)
as
(
select intSeriesId,  
       count(*) Dups

  from @mattt
  GROUP BY intSeriesId
  HAVING COUNT(*) = 1
)  
select * 
  from cteMatt cte
  inner join @mattt m
  on cte.intSeriesId = m.intSeriesId

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

mattt
Posting Yak Master

194 Posts

Posted - 2012-04-10 : 12:26:27

Great, thanks.

For some peculiar reason that doesn't work with the actual data set (which is much bigger) but I've taken up enough of your time - I'll figure that out for myself based on the code you've posted.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-10 : 12:43:13

this is the place to take enough of someone else's time :)

doesn't work as in "it is slow" or does not return the results you want?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:45:05

[code]
SELECT t.*
FROM table t
INNER JOIN (SELECT intSeriesId
FROM table
GROUP BY intSeriesId
HAVING COUNT(DISTINCT intBrandId)=1
)t1
ON t1.intSeriesId = t.intSeriesId
[/code]

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

mattt
Posting Yak Master

194 Posts

Posted - 2012-04-11 : 05:22:19

quote:
Originally posted by yosiasz

this is the place to take enough of someone else's time :)

doesn't work as in "it is slow" or does not return the results you want?

Well thanks :)

Doesn't work as in doesn't return the expected results. But I figured out why: this is part of a larger and more complex query and in my haste to test it out I ran it directly against a table when I needed to run it against a subquery instead. All good now.

quote:

Originally posted by visakh16


SELECT t.*
FROM table t 
INNER JOIN (SELECT intSeriesId 
            FROM table
            GROUP BY intSeriesId 
            HAVING COUNT(DISTINCT intBrandId)=1
           )t1
ON t1.intSeriesId = t.intSeriesId

Glad you posted this - this was the technique I tried to implement but my code was buggy and I couldn't see why. Now I can, which is great, so thanks to you both.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-11 : 05:35:58

Replace this " HAVING COUNT(DISTINCT intBrandId) = 1 " with this " HAVING MIN(intBrandID) = MAX(intBrandID) " for performance.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources