RE: SQL FETCH FIRST and columns in ORDER BY

You're correct to want to eliminate the cursor in this case. SELECT
HSACTION INTO :myHsAction FROM ... WHERE ... ORDER BY ... FETCH FIRST ROW
ONLY will perform slightly better and is simpler to code and maintain.

Another possible approach is to use a subselect that gets a MAX of a certain
column (i.e. HSDATE) and then use that in a WHERE clause. I wouldn't go that
route though, unless there was a distinct performance advantage to it.

You need an index over HSSUB,HSACTION and could possibly tack HSDATE to it,
but it's not required. I wouldn't focus on the ORDER BY index at all,
unless you have MANY, MANY rows that match a specific HSSUB value and their
HSACTION is in 'LP','RE'. When I say index, it of course includes a keyed LF
as a possibility as well.

Finally, you use an RRN in the ORDER BY. Is there absolutely no other way
to get the right order? Like another table column to serve as a secondary
order-by column. RRN is not keyed internally so I recommend avoiding it
when possible.
That should not matter much, as the enhancements I mentioned above should
get you performing quite well already.

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: SQL FETCH FIRST and columns in ORDER BY

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