Return top row

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

Min or First come to mind SELECT FIRST 1 * FROM TABLE WHERE condition ORDER BY .... or SELECT * FROM TABLE WHERE wx_week (or whatnot) IN (select min wx_week from table and whatever other conditions) j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of jared.hanks@gmail.com Sent: Thursday, July 19, 2007 10:24 PM To: informix-list@iiug.org Subject: Return top row 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 _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

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