row number函数 oracle,ORACLE中的ROW_NUMBER函数

The ROW_NUMBER function assigns a unique number (sequentially, starting from 1, as defined by ORDER BY) to each row within the partition. It has the following syntax:

ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )

Example 21-6 ROW_NUMBER

SELECT channel_desc, calendar_month_desc,

TO_CHAR(TRUNC(SUM(amount_sold), -5), '9,999,999,999') SALES$,

ROW_NUMBER() OVER (ORDER BY TRUNC(SUM(amount_sold), -6) DESC) AS ROW_NUMBER

FROM sales, products, customers, times, channels

WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id

AND sales.time_id=times.time_id AND sales.channel_id=channels.channel_id

AND times.calendar_month_desc IN ('2001-09', '2001-10')

GROUP BY channel_desc, calendar_month_desc;

CHANNEL_DESC         CALENDAR SALES$         ROW_NUMBER

-------------------- -------- -------------- ----------

Direct Sales         2001-09       1,100,000          1

Direct Sales         2001-10       1,000,000          2

Internet             2001-09         500,000          3

Internet             2001-10         700,000          4

Partners             2001-09         600,000          5

Partners             2001-10         600,000          6

Note that there are three pairs of tie values in these results. Like NTILE, ROW_NUMBER is a non-deterministic function, so each tied value could have its row number switched. To ensure deterministic results, you must order on a unique key. Inmost cases, that will require adding a new tie breaker column to the query and using it in the ORDER BY specification.