Another Query Question

Suppose you have a table in which one of the fields can have any
number of the same values. Is there a way to select on that field for
only those records where there is a single occurrence of that value in
the entire table ? I don't want any records returned by the query if
there is more than one occurrence, just if there's one. Thanks all.

Rick."Rick" <snarfie.mcdougal@.comcast.net> wrote in message
news:7b5ae645.0312110640.3eec56c4@.posting.google.c om...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.

This is one way to do it, using the Northwind database - find any customers
who have only one row in the Orders table:

select * from
Orders t join
(
select CustomerID
from Orders
group by CustomerID
having count(*) = 1
) dt
on t.CustomerID = dt.CustomerID

Simon|||snarfie.mcdougal@.comcast.net (Rick) wrote in message news:<7b5ae645.0312110640.3eec56c4@.posting.google.com>...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.
Hi Rick,

Are you talking about duplicate records? "Values" and "occurences"
are a little ambiguous. -- Louis

create table #T(x int)
insert into #T values(1)
insert into #T values(2)
insert into #T values(2)
insert into #T values(3)
insert into #T values(3)
insert into #T values(3)

select x
from #T
group by x
having count(*)=1

returns:
x
----
1|||snarfie.mcdougal@.comcast.net (Rick) wrote in message news:<7b5ae645.0312110640.3eec56c4@.posting.google.com>...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.

To find single occurrances for a column...

select col1
count(*) as col_cnt
from table1
group by col1
having count(*) = 1;

so to return the rows with single occurances, join the above back to
the original table...

select t.*
from table 1 as t
(select col1
count(*) as col_cnt
from table1
group by col1
having count(*) = 1
) as s
where t.col1 = s.col1;

Christian.|||ok, forgive me cause I'm doing this strictly out of memory, but its
close...

Select col1, count(*)
from mytable
group by col1
having count(*) = 1

or

declare @.tResults TABLE (mycol int, rowcount int)
insert into @.tResults
Select col1, count(*)
from mytable
group by col1
having count(*) = 1

select mycol from @.tResults where rowcount = 1

"Rick" <snarfie.mcdougal@.comcast.net> wrote in message
news:7b5ae645.0312110640.3eec56c4@.posting.google.c om...
> Suppose you have a table in which one of the fields can have any
> number of the same values. Is there a way to select on that field for
> only those records where there is a single occurrence of that value in
> the entire table ? I don't want any records returned by the query if
> there is more than one occurrence, just if there's one. Thanks all.
> Rick.