Home » SQL & PL/SQL » SQL & PL/SQL » Finding entries where only 1 entry is available (merged 3)
Finding entries where only 1 entry is available (merged 3) [message #411008] Wed, 01 July 2009 08:28 Go to next message
dior
Messages: 25
Registered: April 2009
Junior Member
Hi,

I´m trying to perform a sql statement where I have several 1000 entries.

Sample Situtaion:

Table A

FI       number    CI
047913     28d     1
047913     28d     2
047990     89e     1
048154     54a     1
048154     54a     2
048154     54a     3
048181      14     1


the result sould be:

047990     89e     1
048181      14     1


so only the entries where the cid is 1.
but not only the cid is 1, also the fi should be unique, so not entries where the ci is 1 and there is a similar fi and a ci with 2 or 3.

thx


Re: Finding entries where only 1 entry is available (merged 3) [message #411021 is a reply to message #411008] Wed, 01 July 2009 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use COUNT to count the row for each fi, keep only those with is 1 and then keep only those with ci is 1.

Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Finding entries where only 1 entry is available (merged 3) [message #411025 is a reply to message #411021] Wed, 01 July 2009 08:54 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Or something along these lines (note:untested code)
select fi
,      number
,      1
from   table_a
where  ci = 1
minus
select fi
,      number
,      1
from   table_a
where  ci <> 1


Not sure which will be faster
Previous Topic: Update in PL/SQL procedure
Next Topic: how to use multi select ? (merged 2)
Goto Forum:
  


Current Time: Sat Dec 10 02:56:27 CST 2016

Total time taken to generate the page: 0.05260 seconds