Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    Rogue River, Or
    Posts
    5

    Question Unanswered: Comparing 2 records

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select t1.id
         , t1.starttime as starttime
         , t2.starttime as endtime
      from events t1
    inner
      join events t2
        on t1.id = t2.id
       and t1.starttime < t2.starttime
    use left outer join instead of inner if you want to pick up events that have started but not finished

    rudy
    http://r937.com/

  3. #3
    Join Date
    Dec 2003
    Location
    Rogue River, Or
    Posts
    5
    Thanks Rudy!

  4. #4
    Join Date
    Dec 2003
    Location
    Rogue River, Or
    Posts
    5
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The (+) goes on the "outer" side of the join, i.e.

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

Posting Permissions

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