SQL in oracle

Hello Experts,

I have a table like below :

create table id_test (
id number , name varchar2(20)
) ;

Open in new window


And records as below:

ID NAME
1  sr
1  ds
2 sr 
2 ds
3  sr

Open in new window


where I want to select only id's which have only name as 'sr'.
But as I have 100's of names so cannot put a not in condition to check other names.

The expected result would be like :
ID NAME
3  sr

Open in new window

deve_thomosAsked:
Who is Participating?
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
try this :

select x.id , x.name
from id_test x,
( select id, count(distinct name ) cc
from id_test
group by id
having count(distinct name) = 1 ) y
where x.name = 'sr'
and x.id = y.id ;

Thanks,
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
It can be done a lot more ways using a correlated sub query or analytical functions etc and i have just posted one for you to test.

Thanks,
0
 
HuaMin ChenSystem AnalystCommented:
Try
select max(id),name
from id_test
where name='sr'
group by name;

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
I tested the query which i had given in my post and it works fine.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Another vesion using exists is given below and this also works fine for me. Give this a try as well.

select *
from id_test x
where exists ( select 1 from id_test y where x.id = y.id group by id having count( distinct y.name ) = 1 )
and x.name ='sr'

Thanks,
0
 
slightwv (䄆 Netminder) Commented:
Here's a version that only hits the table once.

It has one known flaw if you can have data like:
4  sr
4  sr


select id, name from
(
select id, name, count(*) over(partition by id order by id) rc from tab1
)
where name='sr' and rc=1
/

Open in new window



If you can have duplicate sr's like I mentioned above then try this:
select id, name from
(
select id, name, sum(case when name='dr' then 1 end) over(partition by id order by id) rc from tab1
)
where name='sr' and rc is null
/

Open in new window

0
 
awking00Commented:
>>The expected result would be like :
ID NAME
3  sr <<
Is that because there are three values of 'sr' or because 3 is the highest id where the values is 'sr'?
0
 
slightwv (䄆 Netminder) Commented:
>>Is that because there are three values of 'sr' or because 3 is the highest id where the values is 'sr'?

Here is how I read the requirement:
I need the rows that only have an 'sr' and no 'other' value.

In other words:  all the other id's have some other value with an 'sr'.  I don't believe MAX has anything to do with it.

If the data was:
1 sr
1 xx
2 sr
3 sr
3 qq
4 bb

the result would be:
2 sr
0
 
awking00Commented:
>>The expected result would be like :
ID NAME
3  sr <<
Another possibility. Is that because id of 3 only has a value of 'sr' while ids 1 and 2 have values of 'sr' and other values as well?
0
 
awking00Commented:
slightwv, sorry I was typing while you were responding but, as you can see by my last question, I think your reading of the question is correct.
select id from id_test where name = 'sr'
minus
select id from id_test where name != 'sr'
0
 
slightwv (䄆 Netminder) Commented:
I thought of minus as well but then you have to use it to select back against the main table to get the name.  Not you have hit the table 3 times.
0
 
SurranoSystem EngineerCommented:
select i.id, i.name 
from id_name i left join 
  (select id, count(*) c from id_name where name <> 'sr' group by id) ii on (i.id=ii.id)
where i.name='sr' and ii.c is null;

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Surrano's post looks like it works but it also accesses the table more than once.
0
 
deve_thomosAuthor Commented:
Thanks a ton...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.