Results 1 to 5 of 5

Thread: rownum

  1. #1
    Join Date
    Sep 2011
    Location
    Hyderabad
    Posts
    18

    Unanswered: rownum

    why rownum not working if you comparing with >?
    ex:-
    select * from emp
    where rownum>2;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Believe it or not, Oracle has actually undertaken the effort to explain this behaviour.

    They call it "The manual":

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/pseudocolumns009.htm#i1006297

  3. #3
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Refer the link below or the para which is from the link

    6 Performing SQL Operations from PL/SQL


    ROWNUM

    ROWNUM returns a number indicating the order in which a row was selected from a table. The first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the retrieved rows before the sort is done; use a subselect (shown in the following example) to get the first n sorted rows.

    You can use ROWNUM in an UPDATE statement to assign unique values to each row in a table, or in the WHERE clause of a SELECT statement to limit the number of rows retrieved:

    CREATE TABLE employees_temp AS SELECT * FROM employees;

    DECLARE
    CURSOR c1 IS SELECT employee_id, salary FROM employees_temp
    WHERE salary > 2000 AND ROWNUM <= 10; -- 10 arbitrary rows
    CURSOR c2 IS SELECT * FROM
    (SELECT employee_id, salary FROM employees_temp
    WHERE salary > 2000 ORDER BY salary DESC)
    WHERE ROWNUM < 5; -- first 5 rows, in sorted order
    BEGIN
    -- Each row gets assigned a different number
    UPDATE employees_temp SET employee_id = ROWNUM;
    END;
    /

    DROP TABLE employees_temp;

    The value of ROWNUM increases only when a row is retrieved, so the only meaningful uses of ROWNUM in a WHERE clause are

    ... WHERE ROWNUM < constant;
    ... WHERE ROWNUM <= constant;

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You could do it like
    Code:
    select select col1,col2,col3,col4
    from
    (
    select col1,col2,col3,col4,rownum rnum
    from
    (select col1,col2,col3,col4 
    from emp
    order by col1)))
    where rnum > 2;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT col1, 
           col2, 
           col3,
    FROM ( 
       SELECT col1, 
              col2, 
              col3,
              row_number() over (order by col1) as rn
       FROM the_table
    ) t 
    WHERE rn > 2

Posting Permissions

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