selecting first record from an ordered list in pl*sql

Alaska Cowboy
Alaska Cowboy used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
why not make sure the query returns max 1 record?
also, if you only need 1 value, why not use:
SELECT column
   INTO variable
 FROM ...
 WHERE  ...

Open in new window

Author

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

Most Valuable Expert 2011
Top Expert 2012
Commented:
if the cursor is already ordered


select * from ( ... your original query ... )
where rownum = 1
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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 !
Most Valuable Expert 2011
Top Expert 2012

Commented:
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.

Most Valuable Expert 2011
Top Expert 2012

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

Author

Commented:
ok, thanks, I see the STOPKEY sort restriction

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial