Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: need help with complicated query

    hey,

    I have this table im my db:

    (orders by time)

    id | time (timestamp)
    0 _______ 0
    3 _______2
    2 _______ 10
    5 _______15
    4 _______18

    I want to get for each id, it's timestamp, and the timestamp for the next row.
    for example (the right answer for the upper table):
    id | start_time | end_time
    0 _____0 _______ 2
    3 _______2 _______10
    2 _______10 _______15
    5 _______15 _______18

    so what is you suggestions? how to build my query?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    select id, 
           time_value as start_time, 
           lead(time_value) over (order by time_value) as end_time
    from the_table_with_no_name

  3. #3
    Join Date
    Dec 2010
    Posts
    3
    I'm getting this error:

    ERROR: syntax error at or near "over" at character 82

    I'm using pgsql 8.1, is that possible that there is no support for 'over' clause?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    untested...
    Code:
    SELECT r1.id
         , r1.time AS start_time
         , r2.time AS end_time
      FROM daTable AS r1
    INNER
      JOIN daTable AS r2
        ON r2.time =
           ( SELECT MIN(time)
               FROM daTable
              WHERE time > r1.time )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by pitbull969 View Post
    I'm using pgsql 8.1, is that possible that there is no support for 'over' clause?
    As you did not post your version, I was assuming you were using the latest one.

    But 8.1 is no longer supported, you should upgrade to a supported version (I recommend at least 8.4) as soon as possible!

  6. #6
    Join Date
    Dec 2010
    Posts
    3
    r937 , Thanks!

    it works...

    now what if I want to do further processing on the produced table:
    id | start_time | end_time
    0 _____0 _______ 2
    3 _______1 _______10
    2 _______8 _______15
    5 _______0 _______18

    I want to get all the id's that their start_time and end_time are overlapped.
    example (for the upper table):

    overlapped_ids | overlap_start_time | overlap_end_time
    "0,3,5"_____________ 1 ______________ 2
    "3,2,5"_____________ 8 ______________ 10
    "2,5"_______________8_______________15

    I hope it's clear enough...

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pitbull969 View Post
    I hope it's clear enough...
    sorry, it isn't

    but in any case, i have a feeling you will need to do this type of data analysis using arrays in your application language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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