Re: SQL FETCH FIRST and columns in ORDER BY

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Hi Dennis

What defines "last"? It looks like the most recent HSDATE for the sub 
and actions in question. Maybe try -

Declare cHist Cursor for 

       SELECT HSACTION
         FROM HISTORY
        WHERE HSSUB = :thisSub
          AND HSACTION IN ('LP','RE')
          AND HSDATE = (SELECT MAX(HSDATE)
                          FROM HISTORY
                        WHERE HSSUB = :thisSub
                        AND HSACTION IN ('LP','RE'))

A suitable JOIN might do it, also - not sure at this early hour about the WHERE clause in the subquery. Or in the main query. HSDATE being unique for the rest of the conditions would simplify things - but that's not likely. You do want and index on HSDATE, I think.

HTH
Vern

Dennis Lovelady wrote:

> 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 
>
>  
>
>