Re: SQL - finding next date - Mailing list pgsql-general

Raymond O'Donnell wrote:
> This is probably a very simple one, but I just can't see the answer and
> it's driving me nuts. I have a table holding details of academic terms,
> and I need an SQL query such that for any given term I want to find the
> next term by starting date (or just NULL if there isn't one).

Here's one approach given your table def.

select t.*,
  (  select term_id
     from terms
     where term_starts > t.term_ends
     order by term_starts asc
     limit 1
  ) as next_term_id
from terms t
order by t.term_starts asc;

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/