Home » SQL & PL/SQL » SQL & PL/SQL » select statement (10g)
select statement [message #564407] Thu, 23 August 2012 09:01 Go to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
I am new to oracle. I am creating a view, where in the inner query returns below kind data.

column1   column2
-------------------
a       y
a       y
b       y
b       n
c       y
d       n
I want to extract all column1 values which alway has "y" in column2.
In this case, output has to be

output
------
a
c

Can someone please help.

Thanks,
Anand P.
Re: select statement [message #564408 is a reply to message #564407] Thu, 23 August 2012 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: select statement [message #564409 is a reply to message #564407] Thu, 23 August 2012 09:06 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
I can do it with below query

select column1 from table
minus
select column1 from table wher column2='n'


But Since Iam using inner query, don't want to use the table name twice...
Re: select statement [message #564410 is a reply to message #564409] Thu, 23 August 2012 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
sometimes we need to do things even though we don't want to do so.
Re: select statement [message #564411 is a reply to message #564410] Thu, 23 August 2012 09:25 Go to previous messageGo to next message
anandapani
Messages: 14
Registered: July 2009
Junior Member
Hi Swan,
If you have a better way of building the query, it would be helpful to me. Many thanks
Re: select statement [message #564412 is a reply to message #564411] Thu, 23 August 2012 09:30 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
Since we don't have your tables or data, we can not compile, run or test posted code.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

>If you have a better way of building the query.
I have NO clear idea what you actually expect or desire as result set.
Re: select statement [message #564414 is a reply to message #564407] Thu, 23 August 2012 09:41 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
How about:
select column1
from   testtable
group by column1
having min(column2) = 'y' and max(column2) = 'y'

You can skip the "and max(column2) = 'y'" if the only possible values are 'y' and 'n'. (Or just make it 'min(column2) = max(column2)' or 'and count(distinct column2) = 1'.)

[Updated on: Thu, 23 August 2012 09:54]

Report message to a moderator

Re: select statement [message #564415 is a reply to message #564414] Thu, 23 August 2012 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How much time will you stay "new to oracle", many with 2 years as you say they are "expert". Wink

select column1
from   testtable
group by column1
having count(*) = count(decode(column2,'y',1))
/


With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And feedback, if not thanks, to the topic when you get answers.
Im' still waiting for your feedback in the this topic where I gave you a complete solution for your problem where you also said "Can anybody suggest or give a sample template would be really helpful.. Awaiting your kind reply frndz...".
So I now say "Awaiting your kind reply frndz..." (on ALL your topics).

Regards
Michel

[Updated on: Thu, 23 August 2012 12:47]

Report message to a moderator

Re: select statement [message #564419 is a reply to message #564407] Thu, 23 August 2012 10:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
Just for fun (and assuming column2 is either y or n):

with sample_table as (
                      select 'a' column1,'y' column2 from dual union all
                      select 'a','y' from dual union all
                      select 'b','y' from dual union all
                      select 'b','n' from dual union all
                      select 'c','y' from dual union all
                      select 'd','n' from dual
                     )
select  column1
  from  sample_table
  group by column1
  having dense_rank('o') within group(order by column2) = 1
/

C
-
a
c

SQL> 


SY.
Re: select statement [message #564428 is a reply to message #564419] Thu, 23 August 2012 12:22 Go to previous messageGo to next message
John Watson
Messages: 4802
Registered: January 2010
Location: Global Village
Senior Member
This is four solutions for the same problem!
I put them through autotrace (it's the end of a long day on the RAC, this is what I do to relax) and the MINUS solution from Anand, though the easiest for me to understand, appears to be the least efficient: multiple scans of the source data. The Cadot and Robertson algorithms are essentially identical, as is (rather to my surprise) the SY solution. My conclusion: I love Solomon's code, even if I often struggle to understand it.
Re: select statement [message #564429 is a reply to message #564428] Thu, 23 August 2012 12:27 Go to previous message
Solomon Yakobson
Messages: 2062
Registered: January 2010
Senior Member
Thanks John,

The reason I said "just for fun" is, indeed, readability. One does have to give it some thought. Letter 'o' is next after 'n', so if dense rank of 'o' placed among all column2 values with same column1 (within group) is 1, then there are no rows with column2 = 'n' within group.

SY.
Previous Topic: Selecting latest date
Next Topic: Ranking Non-Consecutive Rows
Goto Forum:
  


Current Time: Tue Nov 25 22:30:40 CST 2014

Total time taken to generate the page: 0.44188 seconds