Re: Newbie PL/SQL question

From: Andrew Allen <andrew.allen_at_handleman.com>
Date: Mon, 17 Feb 2003 16:14:48 GMT
Message-ID: <3E50FD6C.1020501@handleman.com>

mobiGeek wrote:
> I am new to PL/SQL. I am trying to get the first row from a SELECT
> result set, but am not sure how to go about it.
>
> In ASA, I can do:
>
> SELECT FIRST col1
> INTO @my_variable
> FROM table1
> ORDER BY col2, col3 DESC
>
> This will get the value of "col1" from the FIRST row in the result
> set.
>
> How can I go about this in PL/SQL ?

SELECT col1

   FROM ( SELECT col1

            INTO my_variable
            FROM table1
           ORDER
              BY col2, col3 DESC )

  WHERE rownum = 1;

The inline view is necessary because you have to sort your data before constraining the number of rows to return. Oracle, IIRC, assigns the row number before sending the result set to sort so you have to make sure that you sort first.

--
Andrew Allen

Received on Mon Feb 17 2003 - 10:14:48 CST