Analytic functions

[holdingbe]

Hi all

what is the difference between the rownum and row_number() function

Regards
Michael

[debasisdas]

As you have posted a question in the article section it is being moved to Oracle Forum.

MODERATOR.

[debasisdas]

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause , beginning with 1.

By nesting a subquery using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset of rows from the results of the inner query. This use of the function lets you implement top-N, bottom-N, and inner-N reporting. For consistent results, the query must ensure a deterministic sort order.

You cannot use ROW_NUMBER or any other analytic function for expression. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expr.

Examples
---------------

SELECT deptno, ename, empno, ROW_NUMBER()
   OVER (PARTITION BY deptno ORDER BY empno) AS emp_id FROM emp

ROWNUM
---------------
It is a pseudo column which returns the serial umber of each row in the cursor.