Re: SQL Question

gburnore@netcom.com (Gary L. Burnore) wrote: :Nils Myklebust (Nils.Myklebust@idg.no) wrote: :{ Tin Zaw <tzaw@qualcomm.com> wrote: :{ :Does any one know how to do following in SQL (Informix in particular)? :{ :I need to get the first row a SELECT statement finds, not all the rows. :{ :e.g. SELECT * from my_table where row_value < 10 .... :{ :should return first row with row_value < 10, even the table has :{ :many qualifying rows. :{ There is no direct way of doing this in Informix. :{ If you use some language (4GL, NewEra, C...) you can allways open a :{ cursor and fetch only the first row. :Welll..... :select * from my_table where rowid = (select min(rowid) from : my_table where row_value < 10) :would give you one row. Depending on what you mean by "first row", this :would be it. :Note on versions of informix without a rowid, pick some other unique :field or add one. (Like a serial typed field) :"There is no way ... in Informix" has been said in error so many times, I :can't count. I didn't mean to say "There is no way". The "direct" was meant to say that you can't set the (max) number of rows you want returned from a select statement like you can in some other databases. The given solution will of course work, but sometimes have a small problem. It has to read all rows with row_value < 10 to find the min(rowid). This *may* take a long time. If you can do it (you are working in some language) the approach with a cursor is generally a better solution. Using that approach you may also include an order by to make explisit what is the "first" row if that is needed. Of course the cursor approach may also read a lot of rows in some cases. Sometimes the whole result set has to be generated before the first row can be returned. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org