select term_id from terms where term_id > 2 order by term_starts limit 1; or select term_id from terms where term_starts > '2007-09-01' order by term_starts limit 1;
depending on whether you have the term_id or the term_starts date.
Susan Cassidy
Raymond O'Donnell <rod@iol.ie> Sent by: pgsql-general-owner@postgresql.org
04/11/2007 12:41 PM
Please respond to rod@iol.ie
To
'PostgreSQL' <pgsql-general@postgresql.org>
cc
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, ..... )
- 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
---------------------------------------------------------------------------------------------- Simply protected storage solutions ensure that your information is automatically safe, readily available and always there, visit us at http://www.overlandstorage.com ----------------------------------------------------------------------------------------------