Re: [GENERAL] SQL - finding next date

2007-04-12 Thread Jeffrey Melloy
On 4/11/07, Raymond O'Donnell <[EMAIL PROTECTED]> 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 s

Re: [GENERAL] SQL - finding next date

2007-04-12 Thread Alvaro Herrera
Merlin Moncure escribió: > my suggestion to return the record in a field as a composite type is a > non-standard trick (i think...do composite types exist in the sql > standard?). I think composite types are in the standard, yes, but they are a bit different from what we have. I tried to read th

Re: [GENERAL] SQL - finding next date

2007-04-12 Thread Merlin Moncure
On 4/12/07, Raymond O'Donnell <[EMAIL PROTECTED]> wrote: On 12/04/2007 18:01, Merlin Moncure wrote: > I tested it and this is much faster than 'where exists' solution. Is this an attribute of PostgreSQL in particular, or would it be true of RDBMSs in general? evaluation of subqueries is one p

Re: [GENERAL] SQL - finding next date

2007-04-12 Thread Raymond O'Donnell
On 12/04/2007 18:01, Merlin Moncure wrote: I tested it and this is much faster than 'where exists' solution. Is this an attribute of PostgreSQL in particular, or would it be true of RDBMSs in general? Thanks again, Ray. --- Raymo

Re: [GENERAL] SQL - finding next date

2007-04-12 Thread Merlin Moncure
On 4/12/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: On 4/12/07, Raymond O'Donnell <[EMAIL PROTECTED]> 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 academi

Re: [GENERAL] SQL - finding next date

2007-04-12 Thread Merlin Moncure
On 4/12/07, Raymond O'Donnell <[EMAIL PROTECTED]> 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 particula

Re: [GENERAL] SQL - finding next date

2007-04-12 Thread Raymond O'Donnell
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. Regards,

Re: [GENERAL] SQL - finding next date

2007-04-11 Thread Jon Sime
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

Re: [GENERAL] SQL - finding next date

2007-04-11 Thread Chris Fischer
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- F

Re: [GENERAL] SQL - finding next date

2007-04-11 Thread SCassidy
Is something like this too simple? 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

Re: [GENERAL] SQL - finding next date

2007-04-11 Thread Jeffrey Melloy
On 4/11/07, Raymond O'Donnell <[EMAIL PROTECTED]> 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 s