You'll need to do something like this, called a correlated subquery:
Select t1.term_id, t1.term_name, t1.term_starts, t2.term_id as
next_term
From term t1, term t2
where t2.term_starts = (select min(t3.term_starts) from term t3 where
t3.term_starts > t1.term_starts)
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Raymond
O'Donnell
Sent: Wednesday, April 11, 2007 3:40 PM
To: 'PostgreSQL'
Subject: [GENERAL] SQL - finding next date
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@iol.ie
---------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match