suppressing results(or overriding results) [message #339016] |
Wed, 06 August 2008 09:24  |
kang
Messages: 89 Registered: November 2007
|
Member |
|
|
create table test(
a varchar2(2)
)
insert into test values('10');
insert into test values('11');
insert into test values('20');
insert into test values('21');
insert into test values('30');
insert into test values('31');
insert into test values('40');
insert into test values('51');
the number in the form of [D0] overrides the number in the form of [D1] , so the [D1] should be suppressed.
from the query "select * from test"
I expect
---
10
20
30
40
51
---
11,21,31 should not be there because 10,20,30 are there.
51 should be there because 50 is not there.
this query is so difficult.
help me.
thnaks.
|
|
|
|
Re: suppressing results(or overriding results) [message #339020 is a reply to message #339016] |
Wed, 06 August 2008 10:09   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
create table test_0064(a varchar2(2));
insert into test_0064 values('10');
insert into test_0064 values('11');
insert into test_0064 values('20');
insert into test_0064 values('21');
insert into test_0064 values('30');
insert into test_0064 values('31');
insert into test_0064 values('40');
insert into test_0064 values('51');
select distinct min(a) over (partition by floor(a/10))
from test_0064;
select min(a) keep (dense_rank first order by a)
from test_0064
group by floor(a/10);
|
|
|
|