Re: How to limit number of rows

Arun Use the program to do it, its about the best solution. Your only other choice is to use a stored procedure to do all the work. Mark MarkDenham@aol.com (home) Denham.M@amstr.com (work) --------------------------( Forwarded letter 1 follows )--------------------- Date: Tuesday, 10 June 1997 2:51pm From: jamwal@qcs3.quality.com To: Denham.M@amstr.com Cc: jamwal@qcs3.quality.com Subject: Re: How to limit number of rows SELE Date: Tue, 10 Jun 1997 15:44:10 -0700 Reply-To: jamwal@qcs3.quality.com Hi there! Thanks for you prompt reply. I am using Informix7.2 Online dynamic server for AIX4.2. I am trying to create a temporary table which gets populated from multiple tables, like: select * from orders1 where orders_status = 'A' order by priority INTO TEMP holdall select * from orders2 where orders_status = 'A' order by priority INTO TEMP holdall But the above statements would copy all the rows which satisfy the where clause into the temporary table "holdall". And rowid/rownum solution won't work either because I want to select only a subset of the qualifing rows. using where rowid < 20 will select the first 20 rows in the order of their insertions, I guess. But I want first 20 rows from the result set. In other words, if the above queries return 200 rows each, I just want 50 from the each result set. Do you think it's possible to do that or do I have to retrive "50" rows into my program and then do inserts? Thanks in advance. Arun. > The answer is yes. Assuming that oracle's rownum is analagous to informix's rowid. > > select * > from orders > where rowid < 21> > However this will only be true if none of the rows are deleted. Once rows ae deleted the > number of rows returned will be less than 20. > > If you are using V7.x, V8.x, V9.x informix, then you will have to create the table with > rowids (personally I prefer to add a serial column to the table). This is because rowids > are not guaranteed to be unique if you use fragmentation. > > Why would you want to do this type of query anyhow? > > Mark > MarkDenham@aol.com (home0 > Denham.M@amstr.com (work) > --------------------------( Forwarded letter 1 follows )--------------------- > Date: Tuesday, 10 June 1997 2:01pm > From: A Jamwal <jamwal@quality.com> > Subject: How to limit number of rows SELECTed? > Date: Tue, 10 Jun 1997 12:37:08 -0700 > Reply-To: jamwal@quality.com > Organization: QCS > Sender: informix-list-owner@rmy.emory.edu > To: informix-list@rmy.emory.edu > X-Informix-List-Id: <news.39002> > > Hi everyone, > Is it possible to limit the number of rows > returned by a SELECT statement? For example: > > SELECT * FROM items> > The above statement returns all the rows from the > items table. However, I want to select only, say, > the first 20 rows. How do I restrict the number of > rows SELECTed? In Oracle, one can use rowsnum like: > > SELECT * FROM items > WHERE rownum < 21> > Is there something similar in Informix? > > Thanks in advance. > > Jamwal.