Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    9

    Unanswered: Combining rows in a table(again)

    I've seen a number of questions on combining rows, but not one
    exactly like this. I have a solution, but I'd like to know
    if there are other ways.
    I'd like to select and combine rows from a table. Here's a simplified
    version of the table:
    tab1
    key date status
    1 1/1/06 stat1
    1 1/2/06 stat2
    1 1/3/06 stat3
    1 1/4/06 stat4
    2 1/1/06 stat1
    2 1/2/06 stat2

    And the desired results:
    key date status prevstatus
    1 1/1/06 stat1 null
    1 1/2/06 stat2 stat1
    1 1/3/06 stat3 stat2
    1 1/4/06 stat4 stat3
    2 1/1/06 stat1 null
    2 1/2/06 stat2 stat1

    Here's the simplified version of the solution:
    select
    a.*,b.status prevstatus
    from
    tab1 a
    left join
    tab1 b
    on a.key = b.key and
    b.date =
    (select max(date) from tab1 c
    where
    a.key = c.key and
    a.date > c.date
    )

    Is there a better way?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Your resultset doesn't make much sense. Can you explain it?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    Your resultset doesn't make much sense. Can you explain it?
    It's a "PeopleSoft" join.

    No gams, I'm pretty sure that is optimal for the case you've presented.

    -PatP

  4. #4
    Join Date
    Sep 2005
    Posts
    9
    Yes.
    The idea is to get a row and the most recent previous status. The first row in a set will have no previous status.

  5. #5
    Join Date
    Sep 2005
    Posts
    9
    Quote Originally Posted by Pat Phelan
    It's a "PeopleSoft" join.

    No gams, I'm pretty sure that is optimal for the case you've presented.

    -PatP
    Does that make me a "PeopleSoft" joiner? What is a "PeopleSoft" join?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The "PeopleSoft join" was a reference for Brett's information. Brett is quite familiar with the glories of PeopleSoft.

    PeopleSoft is an ERP package. The PeopleSoft packages use a data representation that often needs to reference the "prior" row based on a presumed sequence.

    -PatP

Posting Permissions

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