Re: newbie question about rownum

From: <prochak_at_my-dejanews.com>
Date: Thu, 27 Aug 1998 16:26:36 GMT
Message-ID: <6s41bs$k5n$1@nnrp1.dejanews.com>

In article <35E367B1.5E969BE1_at_lancour.tcimet.net>, steve lancour <steve_at_lancour.tcimet.net> wrote:
> Rod,
>
> Thanks for the reply. I think I failed in describing accurately what
> I'm trying to do. I need to select any column or columns from any
> table, using any column or combination of columns in both WHERE and
> ORDER-BY clauses, and return only the first record from the result set.
> I might say SELECT FIRSTNAME, LASTNAME, DEPARTMENT FROM EMPLOYEES WHERE
> FIRSTNAME = "BOB" AND AGE > 30 ORDER BY LASTNAME;. I'm only interested
> in the first person on the list (first in this example defined as the
> "bob" who is older than 30 and whose last name appears first on an
> alphabetical listing). Similarly, I might say SELECT FIRSTNAME,
> LASTNAME, DEPARTMENT FROM EMPLOYEES WHERE FIRSTNAME = "BOB" AND AGE > 30
> ORDER BY AGE; and again, only want the first record, this time defining
> "first" as the youngest "bob" older than 30.
>
> Thanks again for your time.
>

Steve,

In this case, you are probably better off writing a PL/SQL procedure. Then you can control the order and the number of rows. In the procedure, simply create a cursor with your ORDER BY clause. OPEN the cursor, FETCH the first row (or however many you want), CLOSE the cursor, and return the results.

If you are just doing a report, you might pass the result back as a single VARCHAR2 with the columns concatenated together. In this case you can use a PL/SQL function and simply call it from SQLPLUS:

SQL> select YOUR_FUNCTION('BOB', 30) from dual; <result shown here>

You could dump the result(s) in a temporary table. Then access them from there.

   SQL> truncate table YOUR_TEMP_TABLE;
   SQL> execute YOUR_PROCEDURE('BOB',30);
   SQL> select * from YOUR_TEMP_TABLE;

Or use the DBMS_OUTPUT package to print the results from the procedure. No temp table is needed here.
SQL> execute YOUR_PROCEDURE('BOB',30); <result shown here>

Let me know if you need help writing the procedure.

--
Ed Prochak
Magic Interface, Ltd. (ORACLE and other programmers available for assignment) 440-498-3702

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Aug 27 1998 - 11:26:36 CDT