today:
56
yesterday:
667
Total:
1,014,926

SQL ROWNUM

admin 2016.05.19 15:43 Views : 246

ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:

SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees
    WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE my_table
    SET column1 = ROWNUM;
No. Subject Author Date Views
106 DBMS_NETWORK_ACL_ADMIN admin 2016.06.07 361
105 ADD_PRIVILEGE (2nd STEP) admin 2016.06.07 360
104 Access Control List (ACL) admin 2016.06.07 350
103 DBMS_NETWORK_ACL_ADMIN admin 2016.06.07 348
102 function get area admin 2016.06.08 334
101 How to set a proxy in UTL_HTTP admin 2016.06.07 329
100 CREATE_ACL (1st STEP) admin 2016.06.07 320
99 ACL 보기 admin 2016.06.07 319
98 Assign a network host to Access Control List admin 2016.06.07 309
97 Oracle 11g Access Control List for External Network Services admin 2016.06.07 306
96 CAPTCHA PLUG-IN admin 2016.05.27 304
95 영수증 2023 file admin 2024.01.04 285
94 SUBSTR admin 2016.05.26 280
93 FOR LOOP admin 2016.05.20 278
92 Validation to detect text in numeric field admin 2018.05.19 254
» ROWNUM admin 2016.05.19 246
90 Change Admin PASSWORD admin 2018.07.31 244
89 Group by, Order by (두개의 Table을 활용한 Sub Total / 최고값 ) admin 2016.05.06 242
88 VERY IMPORTANT admin 2018.06.26 239
87 How-To Document / Change a Logo in a Page Template admin 2016.04.16 239