Select row with only ONE occurence

[das]

Hi all,
How can I get a row that has only one occurence in a table? Not
through 'distinct' because this gets a single row that might have
multiple occurences, I want to get only rows that have only one
occurence in the table.

I tried the group by and within the having clause I placed the
count(myfield) = 1, did not work...
select f1, f2, count(f2)
from table1
inner join table2 on table1.id = table2.fid
where .....
group by f1, f2
having count(f2) = 1

gives me result like this: (basically in this situation, I dont want to
return any results)

f1 f2 count(f1)
--- --- -----------
1 a 1
2 a 1

any ideas sql gurus?

thanks

[Alexander Kuznetsov]

an untested stab in the dark:

select f1, f2 from table1 join
(select f2, count(*) from table1 group by f2 having count(*)=1) t2
on t2.f2=table1.f2

[figital]

The reason you are getting both rows back is because f1 is not
identical in each. You need to remove f1 from the group by and add an
aggregator to the select list.

Something like this:

select max(f1), f2
from table1
inner join table2 on table1.id = table2.fid
where .....
group by f2
having count(f2) = 1

[das]

That is correct, my grouping was wrong.

I will now enclose the inner sql in another one and get the value of
f1.

select f1, f2 from
(.....)

thanks!