| select statement [message #564407] |
Thu, 23 August 2012 09:01  |
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
In this case, output has to be
Can someone please help.
Thanks,
Anand P.
|
|
|
|
|
|
| Re: select statement [message #564409 is a reply to message #564407] |
Thu, 23 August 2012 09:06  |
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 #564414 is a reply to message #564407] |
Thu, 23 August 2012 09:41 |
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 |
 |
Michel Cadot
Messages: 54245
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". 
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 |
Solomon Yakobson
Messages: 1406
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 |
John Watson
Messages: 3113
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 |
Solomon Yakobson
Messages: 1406
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.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
