Home » SQL & PL/SQL » SQL & PL/SQL » To find one row before & after all retrieved rows
|
|
Re: To find one row before & after all retrieved rows [message #35733 is a reply to message #35731] |
Wed, 10 October 2001 12:39 |
sunil bodke
Messages: 2 Registered: October 2001
|
Junior Member |
|
|
I think I was not clear on my question. Consider the following example.
Assume the following table with column c12,c2 & c3
c1 c2 c3
1 2 3
4 5 6
7 8 9
10 11 12
13 14 15
16 17 18
19 20 21
22 33 24
I want all rows where c1 is in 4 & 19 and also rows one before & one after all such retrieved rows.
i.e. rows with c1=4,13,1,7,10,16.
NOTE: c1=1 & c1=7 are rows one before & one after row with c1=4
I think I have made the question clear now.
----------------------------------------------------------------------
|
|
|
Re: To find one row before & after all retrieved rows [message #35735 is a reply to message #35733] |
Wed, 10 October 2001 15:19 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Well, it is sort of clear. I think you mean where c1 is either 4 OR 13 (not 19).
Maybe someone else can come up with a better way here, but this does give you your answer (although it is hardcoded for 2 input numbers):
select * from t
where c1 in (4,13)
or c1 in (select max(c1) from t where c1 < 4)
or c1 in (select min(c1) from t where c1 > 4)
or c1 in (select max(c1) from t where c1 < 13)
or c1 in (select min(c1) from t where c1 > 13)
order by c1
----------------------------------------------------------------------
|
|
|
Re: To find one row before & after all retrieved rows [message #35739 is a reply to message #35733] |
Thu, 11 October 2001 03:53 |
Hans
Messages: 42 Registered: September 2000
|
Member |
|
|
here is another approach. this script works only in 8.1.5 and above
because in 8.0.5 and lower you can't use an order by in an inline view.
drop table t;
create table t (
c1 number(3),
c2 varchar2(3),
c3 number(3)
);
insert into t values ( 1, 'A', 12 );
insert into t values ( 4, 'B', 11 );
insert into t values ( 7, 'C', 12 );
insert into t values ( 10, 'B', 19 );
insert into t values ( 10, 'B', 17 );
insert into t values ( 10, 'C', 18 );
insert into t values ( 13, 'D', 15 );
insert into t values ( 13, 'D', 14 );
insert into t values ( 16, 'B', 18 );
insert into t values ( 19, 'A', 21 );
-- for example this order by clause defines the prior and next row
select * from t
order by c1, c2, c3 desc;
C1 C2 C3
---------- --- ----------
1 A 12
4 B 11
7 C 12
10 B 19
10 B 17
10 C 18
13 D 15
13 D 14
16 B 18
19 A 21
set verify off
undefine where_clause
select c1, c2, c3 from (
select * from (
select rownum rn, c1, c2, c3 from
(
select c1, c2, c3 from t
order by c1, c2, c3 desc
)
)
where rn-1 in
(
select rn from
(
select rownum rn, c1, c2, c3 from
(
select c1, c2, c3 from t
order by c1, c2, c3 desc
)
)
where &&where_clause
)
union
select * from (
select rownum rn, c1, c2, c3 from
(
select c1, c2, c3 from t
order by c1, c2, c3 desc
)
)
where rn in
(
select rn from
(
select rownum rn, c1, c2, c3 from
(
select c1, c2, c3 from t
order by c1, c2, c3 desc
)
)
where &&where_clause
)
union
select * from (
select rownum rn, c1, c2, c3 from
(
select c1, c2, c3 from t
order by c1, c2, c3 desc
)
)
where rn+1 in
(
select rn from
(
select rownum rn, c1, c2, c3 from
(
select c1, c2, c3 from t
order by c1, c2, c3 desc
)
)
where &&where_clause
)
)
order by c1, c2, c3 desc;
Enter value for where_clause: c1 in (4,13)
C1 C2 C3
---------- --- ----------
1 A 12
4 B 11
7 C 12
10 C 18
13 D 15
13 D 14
16 B 18
7 rows selected.
Enter value for where_clause: mod(c1,4)=0
C1 C2 C3
---------- --- ----------
1 A 12
4 B 11
7 C 12
13 D 14
16 B 18
19 A 21
6 rows selected.
----------------------------------------------------------------------
|
|
|
Goto Forum:
Current Time: Wed Jun 12 23:34:06 CDT 2024
|