Thread: SQL - finding next date

SQL - finding next date

From

Raymond O'Donnell

Date:

11 April 2007, 19:40:18

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
---------------------------------------------------------------

Re: SQL - finding next date

From

"Jeffrey Melloy"

Date:

11 April 2007, 21:44:09

On 4/11/07, Raymond O'Donnell <rod@iol.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@iol.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

Re: SQL - finding next date

From

SCassidy@overlandstorage.com

Date:

12 April 2007, 02:40:14

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'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, ..... ) - 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

----------------------------------------------------------------------------------------------
Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at http://www.overlandstorage.com
----------------------------------------------------------------------------------------------

Re: SQL - finding next date

From

"Chris Fischer"

Date:

12 April 2007, 04:17:59

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

Re: SQL - finding next date

From

Jon Sime

Date:

12 April 2007, 04:19:20

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 isn't one).

Here's one approach given your table def.

select t.*,
  (  select term_id
     from terms
     where term_starts > t.term_ends
     order by term_starts asc
     limit 1
  ) as next_term_id
from terms t
order by t.term_starts asc;

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/

Re: SQL - finding next date

From

Raymond O'Donnell

Date:

12 April 2007, 15:11:19

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,

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: SQL - finding next date

From

"Merlin Moncure"

Date:

12 April 2007, 15:49:17

On 4/12/07, Raymond O'Donnell <rod@iol.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;

merlin

Re: SQL - finding next date

From

"Merlin Moncure"

Date:

12 April 2007, 17:02:00

On 4/12/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 4/12/07, Raymond O'Donnell <rod@iol.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

Re: SQL - finding next date

From

Raymond O'Donnell

Date:

12 April 2007, 17:13:12

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.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

Re: SQL - finding next date

From

"Merlin Moncure"

Date:

12 April 2007, 17:38:56

On 4/12/07, Raymond O'Donnell <rod@iol.ie> 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 place where various databases quite a
lot...postgresql   one of the nice things about postgresql is that sql
optimization usually (but not always) entails finding the most direct
query to attack the problem.  other databases might prefer joins or
standard subquery approach (where in/exists, etc).

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?).

merlin

Re: SQL - finding next date

From

Alvaro Herrera

Date:

12 April 2007, 18:17:42

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 that part of the standard
a while back and came up blank, it's too filled with strange constructs.
Too closely linked to tables for one thing; there seems to be no notion
of anonymous or standalone types.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: SQL - finding next date

From

"Jeffrey Melloy"

Date:

12 April 2007, 19:40:25

On 4/11/07, Raymond O'Donnell <rod@iol.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@iol.ie
---------------------------------------------------------------