# Thread: Combining rows in a table(again)

1. Registered User
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. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Your resultset doesn't make much sense. Can you explain it?

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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. Registered User
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. Registered User
Join Date
Sep 2005
Posts
9
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579