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

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

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

Original text of this message

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