| sql [message #651042] |
Tue, 10 May 2016 08:08  |
desmond30
Messages: 41
Registered: November 2009
|
Member |
|
|
create table gtest(x number, y date);
insert into gtest values (1,sysdate);
insert into gtest values (2,sysdate);
insert into gtest values (2,null);
insert into gtest values (3,null);
insert into gtest values (3,null);
commit;
I am working on a query that will give only the values of x that has y as null
the result should give me only 3, though there are nulls for 2, since theres a date populated for 2, we should not see it in the result
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: sql [message #651055 is a reply to message #651045] |
Tue, 10 May 2016 10:51  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Besides unnecessary DISTINCT it is less efficient than using analytic count:
with t as (
select x,
count(y) over(partition by x) cnt
from gtest
)
select x
from t
where cnt = 0
/
The above will scan table gtest once comparing to twice when using minus.
SY.
|
|
|
|
| Re: sql [message #651057 is a reply to message #651042] |
Tue, 10 May 2016 11:00 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
And another solution:orclz>
orclz> select distinct x from gtest where x not in (select x from gtest where y is not null);
X
----------
3
orclz> |
|
|
|
| Re: sql [message #651061 is a reply to message #651057] |
Tue, 10 May 2016 11:31 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Your solution isn't equivalent to OP's (or mine) solutions. It will not work if X is null.
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
