Problem here is the limit is applied BEFORE the sort so the result will not be the correct one.
When I want to grab the first record on an elaborate sort, I create a temp view with the appropriate sort, and select from that view using the LIMIT = 1 clause. The view does the sorting so the first record out is the correct one. CREATE TEMP VIEW tempview ...... ORDER BY ........ SELECT ... FROM tempview WHERE LIMIT = 1 Dennis McGrath --- Bernie Corrigan <[EMAIL PROTECTED]> wrote: > Javier - > > The problem is caused by there being more than one row > in the solution set. Including an indicator variable controls > for only a NULL condition. Using LIMIT = 1 in the WHERE clause > limits the number of rows in the solution set to 1. Like so: > > SELECT MAX(eventdt) INTO event INDICATOR vind1 + > FROM docket + > WHERE (clno = .clnum) + > AND (eventcd = 'stc') + > AND (LIMIT = 1) > > Bernie > > -------------------------------------- > At 04:35 PM 8/6/2004 -0500, you wrote: > >Bernie: > >Although you may get the correct result, it is unpredictable and > poor > >programming practice to use a command that gives you an error > message. You > >should use: > >SELECT MAX(eventdt) INTO event INDICATOR vind1 FROM docket WHERE > clno = > >.clnum and eventcd = 'stc' > >Javier, > > > >Javier Valencia, PE > >President > >Valencia Technology Group, L.L.C. > >14315 S. Twilight Ln, Suite #14 > >Olathe, Kansas 66062-4578 > >Office (913)829-0888 > >Fax (913)649-2904 > >Cell (913)915-3137 > >================================================ > >Attention: > >The information contained in this message and or attachments is > intended > >only for the person or entity to which it is addressed and may > contain > >confidential and/or privileged material. Any review, > retransmission, > >dissemination or other use of, or taking of any action in reliance > upon, > >this information by persons or entities other than the intended > recipient > >is prohibited. If you received this in error, please contact the > sender and > >delete the material from all system and destroy all copies. > >====================================================== > > > >-----Original Message----- > >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > Bernard Lis > >Sent: Friday, August 06, 2004 3:42 PM > >To: RBASE-L Mailing List > >Subject: [RBASE-L] - too many rows returned > > > >Can I count on this happening? > > > >set var event date > >sel eventdt into event from docket where clno=.clnum and eventcd = > 'stc' > >order by eventdt=d > > > >Although it results in the error message "too many rows returned", I > am > >getting the latest date in the variable event. > >This is exactly what I want, can I count on this for all cases? > > > >Bernie Lis > > > > > >
