I have a table that has week, year, and date information. It is
structured so I have a week number, year number, beginning date
(Sundays), and ending date (Saturdays) and these are only by weeks.
For example this is what it looks like:

wx_year wx_week wx_begdate wx_enddate wx_month

2007 1 02/04/2007 02/10/2007 1
2007 2 02/11/2007 02/17/2007 1
2007 3 02/18/2007 02/24/2007 1
2007 4 02/25/2007 03/03/2007 1
2007 5 03/04/2007 03/10/2007 2
2007 6 03/11/2007 03/17/2007 2
2007 7 03/18/2007 03/24/2007 2
2007 8 03/25/2007 03/31/2007 2
2007 9 04/01/2007 04/07/2007 2
2007 10 04/08/2007 04/14/2007 3
2007 11 04/15/2007 04/21/2007 3
and so on....

How could I return the top row only of the wx_begdate record where
wx_month = 1? I can't seem to find it online anywhere.

Thanks,
Jared

On Fri, 20 Jul 2007 02:24:07 -0000, jared.hanks wrote
> I have a table that has week, year, and date information. It is
> structured so I have a week number, year number, beginning date
> (Sundays), and ending date (Saturdays) and these are only by weeks.
> For example this is what it looks like:
>
> wx_year wx_week wx_begdate wx_enddate wx_month
>
> 2007 1 02/04/2007 02/10/2007 1
> 2007 2 02/11/2007 02/17/2007 1
> 2007 3 02/18/2007 02/24/2007 1
> 2007 4 02/25/2007 03/03/2007 1
> 2007 5 03/04/2007 03/10/2007 2
> 2007 6 03/11/2007 03/17/2007 2
> 2007 7 03/18/2007 03/24/2007 2
> 2007 8 03/25/2007 03/31/2007 2
> 2007 9 04/01/2007 04/07/2007 2
> 2007 10 04/08/2007 04/14/2007 3
> 2007 11 04/15/2007 04/21/2007 3
> and so on....
>
> How could I return the top row only of the wx_begdate record where
> wx_month = 1? I can't seem to find it online anywhere.

If your engine is new enough to support SELECT FIRST, you could do "SELECT
FIRST 1 * FROM tablename WHERE ..."

If not, you should help us help you by telling us more about your environment.
Server version, client language, etc.

Then again, your calendar table has a certain code smell to it. Storing
calendar information in the database sounds like a bad idea. I personally
would very much prefer client-side calendar calculations. Consider this: What
date range does your lookup table cover? What do you do when you encounter a
date that's outside your lookup table?

--
Carsten Haese
http://informixdb.sourceforge.net