470,643 Members | 1,374 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,643 developers. It's quick & easy.

Selecting Next Value Only...

I have a table with a number of fields, one being a date.

What I'm looking to do is take a date variable, and then select only
one row - that being the one that contains the next date value in
chronological order. So, as an example, if the table contains

1. 2005-12-01
2. 2006-03-01
3. 2006-06-01
4. 2006-09-01
5. 2006-12-01
6. 2007-03-01
7. 2007-06-01
8. 2007-09-01

I want to run the query and enter the date 2006-10-09, and have the
result returned

1. 2006-12-01

Oct 9 '06 #1
3 1583
th*******@gmail.com wrote:
I have a table with a number of fields, one being a date.

What I'm looking to do is take a date variable, and then select only
one row - that being the one that contains the next date value in
chronological order. So, as an example, if the table contains

1. 2005-12-01
2. 2006-03-01
3. 2006-06-01
4. 2006-09-01
5. 2006-12-01
6. 2007-03-01
7. 2007-06-01
8. 2007-09-01

I want to run the query and enter the date 2006-10-09, and have the
result returned

1. 2006-12-01
SELECT * FROM T
WHERE COLDATE ?
ORDER BY COLDATE
FETCH FIRST ROW ONLY

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 9 '06 #2
That's great Serge- it works :). But, now it's got a little more
complicated...

Imagine in the table

contract date
1. CONTRACT A 2005-12-01
2. CONTRACT B 2006-03-01
3. CONTRACT C 2006-06-01
4. CONTRACT A 2006-09-01
5. CONTRACT B 2006-12-01
6. CONTRACT C 2007-03-01
7. CONTRACT A 2007-06-01
8. CONTRACT B 2007-09-01
9. CONTRACT C 2007-12-01

This time, I'm trying to select the first option after the current
date, but for each contract. So if I run the query with current date as
2006-10-09 the return would be

1. CONTRACT B 2006-12-01
2. CONTRACT C 2007-03-01
3. CONTRACT A 2007-06-01

Serge Rielau wrote:
th*******@gmail.com wrote:
I have a table with a number of fields, one being a date.

What I'm looking to do is take a date variable, and then select only
one row - that being the one that contains the next date value in
chronological order. So, as an example, if the table contains

1. 2005-12-01
2. 2006-03-01
3. 2006-06-01
4. 2006-09-01
5. 2006-12-01
6. 2007-03-01
7. 2007-06-01
8. 2007-09-01

I want to run the query and enter the date 2006-10-09, and have the
result returned

1. 2006-12-01
SELECT * FROM T
WHERE COLDATE ?
ORDER BY COLDATE
FETCH FIRST ROW ONLY

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 9 '06 #3
th*******@gmail.com wrote:
That's great Serge- it works :). But, now it's got a little more
complicated...

Imagine in the table

contract date
1. CONTRACT A 2005-12-01
2. CONTRACT B 2006-03-01
3. CONTRACT C 2006-06-01
4. CONTRACT A 2006-09-01
5. CONTRACT B 2006-12-01
6. CONTRACT C 2007-03-01
7. CONTRACT A 2007-06-01
8. CONTRACT B 2007-09-01
9. CONTRACT C 2007-12-01

This time, I'm trying to select the first option after the current
date, but for each contract. So if I run the query with current date as
2006-10-09 the return would be

1. CONTRACT B 2006-12-01
2. CONTRACT C 2007-03-01
3. CONTRACT A 2007-06-01

SELECT *
FROM (SELECT *
, ROWNUMBER() OVER(GROUP BY contract ORDER BY date) rn
FROM T
WHERE date ?
) S
WHERE rn = 1
ORDER BY contract;

Oct 9 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by areef.islam | last post: by
9 posts views Thread by Generic Usenet Account | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.