Re: SQL - finding next date

On 4/12/07, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On 4/12/07, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
> > On 11/04/2007 21:15, Jon Sime 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,
> >
> > Many thanks indeed to all who replied - I particularly like Jeff's
> > solution, and will use that one.
>
> I think this is by far the cleanest:
>
> select * from term where start_date > (select start_date from term
> where name = 'foo') order by start_date limit 1;

just to clarify, that would be the best way to pick out the next term
from a known term. If you wanted to present the complete list of terms
along with the next sequential term, I would suggest:

select name, (select name from term f where e.start_date >
f.start_date order by f.start_date limit 1) as next_term from term e;

I tested it and this is much faster than 'where exists' solution. If
you want all the properties of the next term, just replace 'select
name' with 'select term' which will return the term as a record
object.

merlin