Re: SQL

From: Gary O'Keefe <gary_at_onegoodidea.com>
Date: Wed, 21 Jul 1999 15:35:07 GMT
Message-ID: <3795d899.106360426@news.hydro.co.uk>

On Wed, 21 Jul 1999 14:04:18 GMT, gary_at_onegoodidea.com (Gary O'Keefe) wrote:

>On Wed, 21 Jul 1999 12:46:14 GMT, narana_at_my-deja.com wrote:
>
>>How do i get (using sql) the top x salary using the emp table?????
>>
>>eg emp1 salary 10
>> emp2 salary 5
>> emp3 salary 6
>> emp4 salary 8
>> emp5 salary 3
>> emp6 salary 2
>>
>>to get the top 3 earners result will be
>>
>> emp1 salary 10
>> emp4 salary 8
>> emp3 salary 6
>>
>>
>>How can i get this result using sql ????????

[stuff snipped]

>SELECT *
>FROM emp
>WHERE rownum <= 3
>ORDER BY salary DESC

I should have known better. This will not work as rownum is decided before ORDER BY. From "ORACLE: The Complete Reference":

"[stuff about DECODE] ...it's important to understand that this number is attached to a row just as it is first pulled from the database, *before* Oracle executes any ORDER BY you've given it." Their emphasis. Maybe if *I* had read the book myself, I wouldn't be eating my words just now.

A better answer might be to use PL/SQL, even though it is a completely bogus language (good for queries, bad for anyone dumb enough to want to look at at the results...):

  set serveroutput on

  DECLARE
    i number;

    cursor c_emp is

      SELECT * from emp
      ORDER BY salary DESC;

    r_emp c_emp%ROWTYPE;

  BEGIN
    dbms_output.enable(1000000);
    OPEN c_emp;
    FOR i in 1..3 LOOP

      fetch c_emp into r_emp;
      exit when c_emp%notfound;
      dbms_output.put_line(r_emp.emp||' '||r_emp.salary);

    END LOOP;
    close c_emp;
  END; A quick explanation for the aspects you may be unfamiliar with:

1. A cursor is a form of select statement that allows the programmer to extract the query results on a row-by-row basis. The order is

  open <cursor>;

  fetch <cursor> into <record>;
  fetch <cursor> into <record>;
  fetch <cursor> into <record>;

  ...
  close <cursor>;

or if you want to iterate through the entire result set of a query

  for <record> in <cursor> loop

• do stuff end loop; 2. The %ROWTYPE operator takes a variable's type and makes a record which holds fields defined to be exactly the same as the original variable. In the case of a cursor the record created has the same number and type of fields as the columns in the cursor's query; 3. dbms_output is one of the standard PL/SQL libraries - enable and put_line are procedures in the libraries; 4. dbms_output is shit (I mean: you have to tell Oracle that you _want to see_ what you are attempting to print with "set serveroutput on", you have a 1Mb limit on the output - no good for large extracts, there's no way to format the output, and about a hundred other brain-dead problems).

Hope this helps more than my last reply. Email me if you need more (either gary.okeefe_at_hydro.co.uk or gary_at_onegoodidea.com).

Gary
--
Gary O'Keefe
gary_at_onegoodidea.com

You know the score - my current employer has nothing to do with what I post Received on Wed Jul 21 1999 - 10:35:07 CDT