By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,236 IT Pros & Developers. It's quick & easy.

Selecting Next Value Only...

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.