asked on

selecting first record from an ordered list in pl*sql

I have a cursor that might return 0, 1, or multiple records.

The query is ordered but I only want the first record.

So what is a good programming construct to do this ?

I have the code below but it's probably ugly to the experienced Oracle programmer . . .

loop_counter := 0;
   
      open idm_ssn_check_cur;
      loop
           loop_counter := loop_counter + 1;
           
           fetch idm_ssn_check_cur into memb_id_nbr_ret;           
           exit when idm_ssn_check_cur%notfound;
           
           if loop_counter = 1 then
              exit;
           end if;                      
           
      end loop;
      
      close idm_ssn_check_cur;

Open in new window

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alaska Cowboy

ASKER

yes, I could do that. I was trying to eliminate using a sub-select to get max(change_dt)

that's cleaner, yes. thanks

select xr.memb_id_nbr as memb_id_found_w_ssn
    from stage3.idm_memb_id_mgt_xref xr
    where xr.memb_ssn = clm_memb_id_nbr_9
   order by xr.load_dt desc;    


can change to

   select xr.memb_id_nbr as memb_id_found_w_ssn
    from stage3.idm_memb_id_mgt_xref xr
    where xr.memb_ssn = clm_memb_id_nbr_9
       and xr.load_dt = (select max(load_dt) 
                         from stage3.idm_memb_id_mgt_xref xr
                         where xr.memb_ssn = clm_memb_id_nbr_9)

Open in new window

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alaska Cowboy

ASKER

sdstuber,

ah, that's good too. I had this situation a while back and tried the query straight up with "where rownum = 1", but it does the "rownum = 1" before it does the order by - but your in-line table does the trick !

thanks !

Sean Stuber

yes, it's essential to do the ordering within a nested (in-line) query

If you generate an explain plan for the query with outer-query rownum you should hopefully see a "COUNT STOPKEY" operation, if so that means the parser recognizes your rownum restriction and will optimize your query on the basis of a fixed upper bound on the query.

Sean Stuber

slight correction -

... the optimizer will "attempt to" optimizer your query on the basis of a fixed upper bound ...

it's only an "attempt", not a guarantee

Alaska Cowboy

ASKER

ok, thanks, I see the STOPKEY sort restriction