On occasion, I have a need in one of my programs to return only one row from
an SQL result set. Case in point is today's issue where we need to
determine the last action from the set "LP" and "RE" against a certain
subscriber. Typically I will set up and open a cursor for this such as the
one I'm about to show you, and will execute a single FETCH, then close the
cursor. But there must be a better way.



Here's a sample cursor:

Declare cHist Cursor for

SELECT HSACTION

FROM HISTORY

WHERE HSSUB = :thisSub

AND HSACTION IN ('LP','RE')

ORDER BY HSDATE DESC

, RRN(HISTORY) DESC



As a partial solution, one of the questions for which I've never located a
definitive answer is "when does FETCH FIRST ROW ONLY" actually participate
in the processing? If I add "FETCH FIRST ROW ONLY" to the end of this
statement, will I get what I want (which is the latest of the selected
transactions), or will the result set be reduced before the ORDER BY? Also
is there another approach entirely that I should be using?



Another, not-quite-related question (since I'm here) is: Would there be
advantage to adding HSSUB to the top of the ORDER BY clause (assuming the
file is indexed by HSSUB, HSDATE (descend)? Or does SQL already "know"
based upon the WHERE clause and handle appropriately? (I tend to believe I
need to state the value in ORDER BY.) If the table is keyed both with and
without the HSSUB column, how would an SQL expert code it?



Dennis E. Lovelady
AIM: delovelady MSN: fastcounter@xxxxxxxxxxxx
<http://www.linkedin.com/in/dennislovelady>
www.linkedin.com/in/dennislovelady --
"Democracy is the worst system devised by the wit of man, except for all the
others."
- Winston Churchill




This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].