Selecting Next Value Only...

[thisisjim]

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

[Serge Rielau]

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/

[thisisjim]

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/

[Tonkuma]

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;