Home » SQL & PL/SQL » SQL & PL/SQL » selecting rows where each value appears exactly once
selecting rows where each value appears exactly once [message #427513] Fri, 23 October 2009 00:57 Go to next message
123soleil
Messages: 35
Registered: July 2006
Member
Hello,

I have the following query:

select distinct c.id, a.key, h.addr
from asset a, asset_house ah, house h, client_house ch, client c
where [link all the tables together]


I would like to select only the rows where the client (c.id) appears exactly once.

my query so far:

select c.id, a.key, h.addr
from asset a, asset_house ah, house h, client_house ch, client c,
(select count(*), id from
(select distinct c.id, a.key, h.addr
from asset a, asset_house ah, house h, client_house ch, client c
where [link all the tables together])
group by id
having count(*) = 1
) sq
where sq.id = c.id
and [link all the tables together]


I'm basically rewriting the main query in the subquery... is there a better way of doing this?
Re: selecting rows where each value appears exactly once [message #427518 is a reply to message #427513] Fri, 23 October 2009 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Using COUNT analytic function.

Regards
Michel
Re: selecting rows where each value appears exactly once [message #427527 is a reply to message #427518] Fri, 23 October 2009 01:54 Go to previous messageGo to next message
123soleil
Messages: 35
Registered: July 2006
Member
Thanks, seems to do exactly what I'm looking for. However, how do I specify the "having count(*) = 1" clause with the analytic function?
Re: selecting rows where each value appears exactly once [message #427529 is a reply to message #427527] Fri, 23 October 2009 02:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no "HAVING" in analytic form, you just select "where cnt=1" (assuming you name the count field "cnt") in outer query:
SQL> select empno, ename, deptno
  2  from (select e.*, count(*) over (partition by deptno) cnt
  3        from emp e)
  4  where cnt=1
  5  /
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7839 KING               10

1 row selected.

Regards
Michel
Re: selecting rows where each value appears exactly once [message #427530 is a reply to message #427513] Fri, 23 October 2009 02:05 Go to previous message
123soleil
Messages: 35
Registered: July 2006
Member
That's what i thought. Many thanks!
Previous Topic: Sequence Start with Value
Next Topic: Concatenate row values
Goto Forum:
  


Current Time: Mon Sep 26 06:03:00 CDT 2016

Total time taken to generate the page: 0.20383 seconds