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
midrange.com
