I'm trying to write an SQL statement that would allow me to compare 2 records from the same table.

Basically, I have a table of events for an object with a column that contains when the event started but not when the event ended. An object in the table will have at least 2 events so I could take the start time of the second event to determine the end time of the first event. Can this be done using SQL? I know I can do it by writing a stored procedure and storing the results in a table, but was hoping not to have to do this.

Any help will be appreciated.

use left outer join instead of inner if you want to pick up events that have started but not finished

rudy
http://r937.com/

Thanks Rudy!

Actually, the version of Oracle I am using did not support "inner join", so what ended up working was:

select t1.id, t1.date, t2.date
from event t1, event t2
where t1.id = t2.id
and t1.date < t2.date

good one, and thanks for the update

i keep forgetting that there are still lots of companies and individuals out there who are in no hurry to install the latest and greatest software

e.g. i'm still on access 97 on my desktop, and have no plans to upgrade ever

by the way, what side does the plus sign go on if you wanted to join event t1 left outer event t2?

i could look it up, i guess...

The (+) goes on the "outer" side of the join, i.e.

t1.id = t2.id (+)
and t1.starttime < t2.starttime (+)