Using a single SQL correlated sub-query to get two columns

Question

My problem is represented by the following query:

SELECT 
  b.row_id, b.x, b.y, b.something,
  (SELECT a.x FROM my_table a WHERE a.row_id = (b.row_id - 1), a.something != 42 ) AS source_x,
  (SELECT a.y FROM my_table a WHERE a.row_id = (b.row_id - 1), a.something != 42 ) AS source_y
FROM 
  my_table b

I'm using the same subquery statement twice, for getting both source_x and source_y. That's why I'm wondering if it's possible to do it using one subquery only?

Because once I run this query on my real data (millions of rows) it seems to never finish and take hours, if not days (my connection hang up before the end).

I am using PostgreSQL 8.4

Answer 1

I think you can use this approach:

SELECT b.row_id
     , b.x
     , b.y
     , b.something
     , a.x
     , a.y
  FROM my_table b
  left join my_table a on a.row_id = (b.row_id - 1)
                      and a.something != 42