26

What is the difference between ROWNUM and ROW_NUMBER ?

mahi_0707
  • 1,030
  • 9
  • 17
Bob Dizzle
  • 1,173
  • 1
  • 8
  • 18

5 Answers5

42

ROWNUM is a "pseudocolumn" that assigns a number to each row returned by a query:

SQL> select rownum, ename, deptno
  2  from emp;

    ROWNUM ENAME          DEPTNO
---------- ---------- ----------
         1 SMITH              99
         2 ALLEN              30
         3 WARD               30
         4 JONES              20
         5 MARTIN             30
         6 BLAKE              30
         7 CLARK              10
         8 SCOTT              20
         9 KING               10
        10 TURNER             30
        11 FORD               20
        12 MILLER             10

ROW_NUMBER is an analytic function that assigns a number to each row according to its ordering within a group of rows:

SQL> select ename, deptno, row_number() over (partition by deptno order by ename) rn
  2  from emp;

ENAME          DEPTNO         RN
---------- ---------- ----------
CLARK              10          1
KING               10          2
MILLER             10          3
FORD               20          1
JONES              20          2
SCOTT              20          3
ALLEN              30          1
BLAKE              30          2
MARTIN             30          3
TURNER             30          4
WARD               30          5
SMITH              99          1
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
1

Apart from the other differences mentioned in answers, you should also consider performance. There is a non-authoritative but very interesting report here, comparing various means of pagination, among which the use of ROWNUM compared to ROW_NUMBER() OVER():

http://www.inf.unideb.hu/~gabora/pagination/results.html

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
1

From a little reading, ROWNUM is a value automatically assigned by Oracle to a rowset (prior to ORDER BY being evaluated, so don't ever ORDER BY ROWNUM or use a WHERE ROWNUM < 10 with an ORDER BY).

ROW_NUMBER() appears to be a function for assigning row numbers to a result set returned by a subquery or partition.

Powerlord
  • 87,612
  • 17
  • 125
  • 175
  • order by is ok e.g., select * from (select a,b,c from foo order by d) where rownum < 10 – Ron Dec 04 '09 at 00:18
  • 3
    @Ron: Try it. You'll find out the hard way that it takes the first 10 results from the entire result set, then orders those. – Powerlord Dec 04 '09 at 14:47
  • @Powerlord: if we use inner query then rownum will be applied on result of inner query – smali Nov 29 '14 at 04:56
-1

rownum is a pseudocolumn which can be added to any select query, to number the rows returned (starting with 1). They are ordered according to when they were identified as being part of the final result set. (#ref)

row_number is an analytic's function, which can be used to number the rows returned by the query in an order mandated by the row_number() function.

Community
  • 1
  • 1
Michael OShea
  • 397
  • 2
  • 7
  • 1
    -1 for "They are numbered in the order that the rows are returned in". Not true -- they are ordered according to when they were identified as being part of the final result set, so that is prior to an ORDER BY clause – David Aldridge Oct 06 '08 at 15:17
-1

Rownum starts with 1 ..increases after condition evaluated results to true . Hence rownum >=1 returns all rows in table

darshan kamat
  • 374
  • 7
  • 23