SELECT TOP 1 FROM.......WHERE.....ORDERBY......... This selects the first row AFTER the sorting is done, so no ambiguity.
Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence Lustig Sent: Monday, July 01, 2013 2:33 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: SELECT <First Row> Part of the "SQL Contract" is that there is no "first row" for any given result set unless you specify a sort order. In R:Base, WHERE LIMIT = 1 will give you a single row, but doesn't guarantee which one. In particular, if your table has a covering index on the columns you're selecting, you'll get the first row from the index, not the first row that you would see if you do SELECT * against the table. That means that SELECT humtpy FROM wall WHERE LIMIT = 1 and SELECT humtpy, dumpty FROM wall WHERE LIMIT = 1 could give you two different rows if wall is indexed on humpty. This can be extremely confusing! (It happens with any database that offers index-only-retrieval, which is most of them). To guard against his, specify a sort order and use TOP 1: SELECT TOP 1 humpty FROM wall ORDER BY wall_id ASC will always return same row of data regardless of how many columns you SELECT. - Larry ________________________________ From: Bruce A. Chitiea <[email protected]<mailto:[email protected]>> To: RBASE-L Mailing List <[email protected]<mailto:[email protected]>> Sent: Monday, July 1, 2013 1:44 PM Subject: [RBASE-L] - SELECT <First Row> All: RBGeX95(64) Current I want to hunt for certain strings within field 'humpty'. This works: DECLARE c1 SCROLL CURSOR FOR + SELECT humpty FROM wall OPEN c1 FETCH FIRST FROM c1 INTO vhumpty IND vind1 <Process> DROP CURSOR c1 Is there any direct means of SELECTing the FIRST row from table without having to resort to a cursor? Thanks Bruce Chitiea SafeSectors, Inc. eCondoMetrics 909.238.9012 cell
