Re: SQL Question

Peter Kolmhofer wrote: > Tin Zaw 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. > Hi Tin, .. (snip).. > SELECT * FROM my_table WHERE row_value < 10 AND rowid = > (SELECT MIN(rowid) FROM my_table WHERE row_value < 10) Hi guys, while the solution Peter has provided (seems to be a correction of Gary's) would probably work (haven't test it) for the Tin's exact question, there are 2 issues being ignored here: 1. The main reason someone usually wants only the "first" row of a query is to not waste time retrieving more rows than needed. The subquery defeats this purpose. 2. A more commonly requested feature is the ability to tell the engine: Retrieve only the first N rows of this query. This is a feature Informix simply does not offer [at this time]. The workaround might be to define what you mean by "first N" with some ORDER BY clause, then use the "select bottom N" query. I won't get into it here (I NEVER remember it & always have to rederive it from scratch. :-<( ) but that is always a correlated subquery, which REALLY defeats the purpose of the "first N". The upshot (or downside) of all this is there really is no work-saving way to get the first N rows of a query. If Informix will offer such a feature depends on some factors, but I believe they are a bit skittish about offering a feature to the SQL "language" not specifically approved by ANSI. (Afraid they won't fit into the "one-size-fits-all" strait jacket? ;) BTW, Peter, you mentioned that rowid's are treated differently in 7.1. Just to clear up a possible misconception: If you do not fragment a table across dbspaces (i.e. the entire table & all its indexes are in the same dbspace, as you've been doing in 5.x) then rowid means the same thing now as it did before. If you DO fragment the table, then the true rowid is a composite of a tblspace-id and an old-style rowid. Not too usable in your apps that (shouldn't have but) used integer rowids. Thus, when you select rowid on a fragmented table, you will get some kind of error at runtime. For compatibility's sake, Informix very thoughtfully supplied an extra clause in the the "create table" statement: "WITH ROWID". This adds an integer column named rowid to the table and creates a unique index on that rowid column. It won't work nearly as fast as rowid used to work, but at least your code won't be broken. Also, I think this imposes a 2-billion row limit on your table. I don't think this is a sever limit for most applications. (If it is, don't use rowid.) Either way, Peter, your solution should work with rowid's