Is there a way to limit the left outer join?

Question

I have a log table with the following format (and sample data):

date | time 2010/01/02 | 10:00 2010/01/02 | 13:00 2010/01/04 | 02:34 

I want to make an SQL query that will receive the results in the following format:

 date1 | time1 | date2 | time2 2010/01/02 | 10:00 | 2010/01/02 | 13:00 2010/01/02 | 13:00 | 2010/01/04 | 02:34 2010/01/04 | 02:34 | <null> | <null> 

So, I thought the left outer join would do the trick:

 SELECT i.date as date1, i.time as time1, j.date as date2, j.time as time2 FROM log_table i LEFT OUTER JOIN log_table j ON i.id = j.id AND (j.date > i.date OR (j.date == i.date AND j.time > i.time)) 

However, this will result in the following:

 date1 | time1 | date2 | time2 2010/01/02 | 10:00 | 2010/01/02 | 13:00 2010/01/02 | 10:00 | 2010/01/04 | 02:34 2010/01/02 | 13:00 | 2010/01/04 | 02:34 2010/01/04 | 02:34 | <null> | <null> 

The database is PostgreSql.

Thanks.

Answer 1

In PostgreSQL 8.4 :

 SELECT date AS date1, time AS time1, LEAD(date) OVER (ORDER BY date, time, id) AS date2, LEAD(time) OVER (ORDER BY date, time, id) AS time2 FROM log_table ORDER BY date, time, id 

or just this:

 SELECT date1, time1, (lnext).* FROM ( SELECT date AS date1, time AS time1, LEAD(lt) OVER (ORDER BY date, time, id) AS lnext FROM log_table lt ) q ORDER BY date, time, id 

In PostgreSQL 8.3 and below:

 SELECT date AS date1, time AS time1, (li).date AS date2, (li).time AS time2 FROM ( SELECT lo.*, ( SELECT li FROM log_table li WHERE (li.date, li.time, li.id) > (lo.date, lo.time, lo.id) ORDER BY date, time, id LIMIT 1 ) AS li FROM log_table lo ) q 

Answer 2

 SELECT i.date as date1, i.time as time1, min(j.date) as date2, min(j.time) as time2 FROM log_table i LEFT OUTER JOIN log_table j ON i.id = j.id AND (j.date > i.date OR (j.date == i.date AND j.time > i.time)) GROUP BY i.date, i.time