Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Getting the first row from a select

Re: Getting the first row from a select

From: <dhollida_at_my-dejanews.com>
Date: 1998/10/02
Message-ID: <6v37ub$2e7$1@nnrp1.dejanews.com>#1/1

> >>> select * from COMPANY where COMPANY_NAME >= 'whatever' and ROWNUM = 1
 order
> >>> by COMPANY_NAME;

This query (without the ROWNUM = ) will open a cursor and retrieve all of the rows in the result set. If you want only the first row of a sorted result set you have two possible solutions in Oracle.

  1. Easiest. Create an index on Company_Name. Issue the same query but without the ORDER BY. Oracle will use the index to find the result set and ROWNUM = 1 will return the one you want. I don't know if this is portable between databases.
  2. Most portable. DECLARE my_cursor CURSOR FOR SELECT * FROM Company WHERE company_name >= 'whatever' ORDER BY company_name;
       OPEN CURSOR my_cursor
       FETCH my_cursor
       INTO
          variable_company_name
          .
          .
          .;
       CLOSE CURSOR;

BTW, SELECT * can get your code into trouble if you later add or remove columns from a table, or rebuild the table and the order of the columns changes. Always explicitely declare the columns in the SELECT list.

Hope this helps.

--
Don Holliday

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    
Received on Fri Oct 02 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US