Re: Getting the first row from a select

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