Home » SQL & PL/SQL » SQL & PL/SQL » suppressing results(or overriding results) (9i,10g,11g)
suppressing results(or overriding results) [message #339016] Wed, 06 August 2008 09:24 Go to next message
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 #339018 is a reply to message #339016] Wed, 06 August 2008 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
for instance,
delete ... a where exists (select from ... b where b.col = a.col+1)

Another way is to use analytical function, another group... there are many solutions.

Regards
Michel
Re: suppressing results(or overriding results) [message #339020 is a reply to message #339016] Wed, 06 August 2008 10:09 Go to previous messageGo to next message
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);
Re: suppressing results(or overriding results) [message #339130 is a reply to message #339020] Wed, 06 August 2008 19:05 Go to previous message
kang
Messages: 89
Registered: November 2007
Member
great.
thanks a lot.
Previous Topic: size of objects
Next Topic: Parsing and searching for each row
Goto Forum:
  


Current Time: Fri Dec 02 12:31:15 CST 2016

Total time taken to generate the page: 0.05625 seconds