Re: prev/next without select *

atlasoft@pi.net (Marcel) wrote: :I work with a large database. When I do a Select resulting in 1 record, is :it possible to do a next or previous in any order without selecting the :whole database ? I don't want to post a SELECT of all the records first :and then skip within the selection. SELECT * takes to much time. If you have selected one row from a table there is no such thing as a "next" or "previous" row. A table is a set and a set by definition is unordered. What you want is probably to read the row that has a higer or lower value in one or more fields then the current row. If the field you use isn't unique (the primary or an alternate key) you also have to take equality of the field into count. :For example: :Is there a way to post a select with an expression and than only receive :the first record matching the expression? :Any good ideas? prepare c_somecursor from select something... from sometable(s) where somefield > currentvalue order by somefieldopen c_somecursor fetch c_somecursor into p_something... You will get the next row in the order of somefiled. Further fetches now will give you the next row again. If you are not carefull whith what you select, how the where clause is written, what field you order by and how your indexes are created the above open/fetch might easily build a temporary table of the whole answer set before the first row is return. To avoide that you essentially have to have an index on the field you order by. If you need to test on (include in the where part) several field and order by these you need a composit index on those fields in the same order as in the order by part of the select. As soon as you start doing joins it is very hard to make sure you avoid building a temporary table. Even if you are able to avoide it onse, the next day (or second) the optimiser may chose another way to do the join and a temporary table is built. Hope this helps. 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