Selecting distinct on two columns and grouping by row count

[albitzt]

Seeking assistance on this one...

Sample Data Set:

rsrc_hr_id rsrc_id ts_id
----------- ----------- -----------
100 200 300
101 200 301
102 201 302
103 201 302

Looking for help with a query to query the data set where it pulls the distinct combinations of rsrc_id and ts_id where count is equal to one.

In the above example the output of what I want would be:

rsrc_id ts_id count
----------- ----------- ----------
200 300 1
200 301 1

Thanks in advance.

 

[jbenson001]

select rsrc_id, ts_id, count(*) as [Count]
from your table
where your criteria
group by rsrc_id, ts_id
Having count = 1

 

[gmmastros]

jbenson001,

You cannot use a column alias in the having clause, so the query would need to be:

select rsrc_id, ts_id, count(*) as [Count]
from your table
where your criteria
group by rsrc_id, ts_id
Having count[!](*)[/!] = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[albitzt]

Thanks jbenson001 for the attempt. And thank you gmmastros for the solution. Worked like a charm.

 

[jbenson001]

I was close, thanks for pointing that out George