query help

[prodadmin]

hello

i have the following data

order# Revision revised_by revised_DATE
======================================
134 2 user1 Nov-03-2008 13:34:14
134 3 user2 Nov-03-2008 16:16:51
134 4 user3 Nov-03-2008 16:31:56
134 5 user4 Nov-05-2008 15:32:39
134 6 user5 Dec-05-2008 12:51:24
135 2 user3 Nov-04-2008 12:03:10
135 3 user4 Nov-05-2008 00:56:39
135 4 user2 Nov-05-2008 02:05:22
135 5 user3 Dec-01-2008 13:44:39
135 6 user6 Dec-01-2008 14:00:14
135 7 user7 Dec-01-2008 14:00:42
135 8 user5 Dec-05-2008 11:41:33

but want to dispaly in this way

order initial revise next revise
134 user1 user2
134 user2 user3
134 user3 user4
134 user4 user5
135 user3 user4
135 user4 user2
135 user2 user3
135 user3 user5
135 user6 user7
135 user7 user5


any help is much appreciated

[davey23uk]

look up stragg on asktom.oracle.com

[ebrian]

One option could be:

SQL> select order#, rb initial_revise, nr next_revise
  2  from (
  3     select order#,
  4            revised_by rb,
  5            order_date od,
  6            lead(revised_by) over (partition by order# order by order_date) nr
  7     from orders)
  8  where nr is not null
  9  order by 1, od;

    ORDER# INITIAL_REVISE       NEXT_REVISE
---------- -------------------- --------------------
       134 user1                user2
       134 user2                user3
       134 user3                user4
       134 user4                user5
       135 user3                user4
       135 user4                user2
       135 user2                user3
       135 user3                user6
       135 user6                user7
       135 user7                user5

10 rows selected.

[prodadmin]

thanks to both of you

much appreciated