Home » SQL & PL/SQL » SQL & PL/SQL » To find one row before & after all retrieved rows
To find one row before & after all retrieved rows [message #35728] Wed, 10 October 2001 12:11 Go to next message
sunil bodke
Messages: 2
Registered: October 2001
Junior Member
How to find rows from a table depending on a condition and then to find one row before and after all such rows

----------------------------------------------------------------------
Re: To find one row before & after all retrieved rows [message #35731 is a reply to message #35728] Wed, 10 October 2001 12:25 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
There is no concept of "before" or "after" - if you are specifying a condition, then only rows that meet your criteria will be included in the result set.

----------------------------------------------------------------------
Re: To find one row before & after all retrieved rows [message #35733 is a reply to message #35731] Wed, 10 October 2001 12:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.



----------------------------------------------------------------------
Previous Topic: DBMS_SQL Table/Column Comment Problem
Next Topic: Coping database objects from one schema to another - Urgent Please help
Goto Forum:
  


Current Time: Sat May 25 15:11:01 CDT 2024