query help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: query help

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    query help

    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
    Last edited by prodadmin; 12-19-2008 at 11:26 AM.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    look up stragg on asktom.oracle.com

  3. #3
    Join Date
    Apr 2006
    Posts
    377
    One option could be:

    Code:
    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.

  4. #4
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    thanks to both of you

    much appreciated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width