Re: SQL - finding next date

On 4/11/07, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
>
> Hi all,
>
> 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 the table -
>
> CREATE TABLE terms
> (
> term_id serial NOT NULL,
> term_name character varying(40) NOT NULL,
> term_starts date NOT NULL,
> term_ends date NOT NULL,
> .....
> )
>
> - so, supposing I have the following data -
>
> term_id | term_name | term_starts | ...
> ---------+-------------+-------------+--
> 1 | Spring 2007 | 2007-01-10 | ...
> 2 | Autumn 2007 | 2007-09-01 | ...
> 6 | Spring 2008 | 2008-01-06 | ...
>
> - then for term '1' I'd like to return '2', for term '2' I'd like to
> return '6', and so on.
>
> The closest I've got is getting ALL terms that start after a given one,
> but I run into trouble after that....any help will be appreciated!
>
> Thanks in advance,
>
> Ray.
>
> ---------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod(at)iol(dot)ie
> ---------------------------------------------------------------

SELECT main.term_name,
main.term_starts mts,
next.term_name,
next.term_starts nts
FROM terms main
LEFT JOIN terms NEXT
ON main.term_starts < NEXT.term_starts
WHERE NOT EXISTS
(SELECT 1
FROM terms t
WHERE t.term_starts > main.term_starts
AND t.term_starts < NEXT.term_starts)

It's kind of a sneak attack way of getting at the min(term_starts) without
using an aggregate.

Jeff