RE: TOP 100 in SQL

another way to exit out of the FOREACH SELECT loop is to use the EXIT FOREACH; statement once a condition has been met. if counter = 100 then exit foreach; end if - Thomas Tatum 919-767-2129 ______________________________________________________________________ Stay connected with family and friends this holiday season and year-round with a FREE, easy-to-use Family Web Site. No HTML required! http://www.familyshoebox.com (this site is powered by informix IUS, BTW) > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org]On Behalf Of Paul Aanei > Sent: Friday, November 13, 1998 5:27 PM > To: informix-list@iiug.org > Subject: Re: TOP 100 in SQL > > > > rfurdzik@paulweiss.com wrote: > > > How to display only first 100 rows from a query ??? Please help > > > > Select a, sum(b) > > group by a DESC> > > > Try a calling a stored procedure like this one: > > create procedure proc() returning integer, integer;> define a integer; > define sumb integer; > define cnt integer; > > let cnt=0; > foreach select a, sum(b) into a, sumb from x group by a desc > if cnt > 100 then > return; > end if; > let cnt = cnt + 1; > return a, sumb with resume; > end foreach; > > end procedure; > > It is true that it doesn't perform as smooth as the "first N" clause > (where this one functions) but it has the advantage that it is way > more complicated. > > Paul Aanei > ------------------------------------------------------------- > Epitaph: > Here lyes Paul Valery - killed by the others. > ------------------------------------------------------------- >