Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2010
    Posts
    10

    Unanswered: Oracle ROWNUM and ORDER BY

    Hi there am trying to run the following:

    Code:
     FUNCTION nth_highest_salary (nth_value NUMBER) RETURN emp%ROWTYPE IS
        v_emp_rec emp%ROWTYPE;
        CURSOR cur_nth_highest IS
        SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
      (SELECT rowid FROM emp WHERE rownum < nth_value);
      BEGIN     OPEN cur_nth_highest;
          LOOP
            FETCH cur_nth_highest INTO v_emp_rec;
            EXIT WHEN cur_nth_highest%NOTFOUND;
            RETURN v_emp_rec;
          END LOOP;
        CLOSE cur_nth_highest;
      END nth_highest_salary;
    It works and returns the ROWNUM record that was passed in, but i need to ORDER BY salary DESC before assigning the ROWNUM.

    I cant seem to workout where this command fits within this code.

    Basically what im trying to do is pass in an nth value and return the record with that ROWNUM ordered by salary DESC.

    Please Help
    Last edited by bbalistic8; 12-04-10 at 20:33.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT * 
    FROM (
     SELECT emp.*, row_number() over (ORDER BY salary DESC) as rn
     FROM emp 
    ) 
    WHERE rn = nth_value;
    For future posts, please use [code] tags to make the code readable.

  3. #3
    Join Date
    Dec 2010
    Posts
    10
    Thanks heaps, but im now getting error with FETCH statement, wrong number of values into list of a fetch statement.
    This was working before but now with your code it doesnt work. What i have got is:
    Code:
    v_emp_rec emp%ROWTYPE;
    BEGIN
        OPEN cur_nth_highest;
          LOOP
            FETCH cur_nth_highest INTO v_emp_rec;
            EXIT WHEN cur_nth_highest%NOTFOUND;
            RETURN v_emp_rec;
          END LOOP;
        CLOSE cur_nth_highest;
      END nth_highest_salary;
    Any ideas?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    That's an effect of using * in all your queries. You need to explicitely state the columns from the emp table in the outer query of my example (because the * now includes the new column "rn")

  5. #5
    Join Date
    Dec 2010
    Posts
    10
    Sorry only new at SQL, im not sure how i would fix that.
    What the call to my function returns is salary and employee_id. From the one record ROWTYPE record returned in my Function.

    I have changed the * to salary and employee_id in the first select statement but i am still getting the same error.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can see code and can not debug it.
    We can not see your code and can not debug what we can not see.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Dec 2010
    Posts
    10
    Here is all the code for that part:

    Code:
      FUNCTION nth_highest_salary (nth_value NUMBER) RETURN emp%ROWTYPE IS
        v_emp_rec emp%ROWTYPE;
        CURSOR cur_nth_highest IS
        SELECT * FROM (SELECT emp.*, row_number() over (ORDER BY salary DESC) as rn FROM emp) 
        WHERE rn = nth_value;
      BEGIN                                   --Returns the record of an employee who has the nth (passed in value) highest salary
        OPEN cur_nth_highest;
          LOOP
            FETCH cur_nth_highest INTO v_emp_rec;
            EXIT WHEN cur_nth_highest%NOTFOUND;
            RETURN v_emp_rec;
          END LOOP;
        CLOSE cur_nth_highest;
      END nth_highest_salary;
    
    
    p_emp_rec := emp_admin.nth_highest_salary(10);
      DBMS_OUTPUT.PUT_LINE('The 10th highest salary is $'|| TO_CHAR(p_emp_rec.salary) || 
        ', belonging to employee: ' || TO_CHAR(p_emp_rec.employee_id) );
      p_emp_rec := emp_admin.nth_highest_salary(12);
      DBMS_OUTPUT.PUT_LINE('The 12th highest salary is $'|| TO_CHAR(p_emp_rec.salary) || 
        ', belonging to employee: ' || TO_CHAR(p_emp_rec.employee_id) );

  8. #8
    Join Date
    Dec 2010
    Posts
    10
    Thanks for your help.

    I have realised what my mistake was. I have updated my code to this and it works.

    Code:
      FUNCTION nth_highest_salary (nth_value NUMBER) RETURN emp%ROWTYPE IS
        v_emp_rec emp%ROWTYPE;
        CURSOR cur_nth_highest IS
        SELECT salary, employee_id FROM (SELECT emp.*, row_number() over (ORDER BY salary DESC) as rn FROM emp) 
        WHERE rn = nth_value;
      BEGIN
        OPEN cur_nth_highest;
          LOOP
            FETCH cur_nth_highest INTO v_emp_rec.salary, v_emp_rec.employee_id;
            EXIT WHEN cur_nth_highest%NOTFOUND;
            RETURN v_emp_rec;
          END LOOP;
        CLOSE cur_nth_highest;
      END nth_highest_salary;
    It works but has some unusual issues,

    When returning the 10th row it works fine. When returning anything between 11 and 14 it returns the 11th row always.

    Is there something wrong with what ive done?

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL>   create or replace FUNCTION nth_highest_salary (nth_value NUMBER) RETURN employees%ROWTYPE IS
      2  	 v_emp_rec employees%ROWTYPE;
      3    BEGIN
      4  	 SELECT salary, employee_id
      5  	 INTO v_emp_rec.salary, v_emp_rec.employee_id
      6  	 FROM (SELECT employees.*, row_number() over (ORDER BY salary DESC) as rn FROM employees) WHERE rn = nth_value;
      7  	 RETURN v_emp_rec;
      8    END nth_highest_salary;
      9  /
    
    Function created.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •