Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

ROWNUM/INST_NUM()

Description

The ROWNUM function returns the number representing the order of the records that will be generated by the query result. The first result record is assigned 1, and the second result record is assigned 2.

ROWNUM and INST_NUM() can be used in the SELECT statement, and GROUPBY_NUM() can be used in the SELECT statement with GROUP BY clauses. The ROWNUM function can be used to limit the number of result records of the query in several ways. For example, it can be used to search only the first 10 records or to return even or odd number records.

The ROWNUM function has a result value as an integer, and can be used wherever an expression is valid such as the SELECT or WHERE clause. However, it is not allowed to compare the result of the ROWNUM function with the attribute or the correlated subquery.

Syntax

INST_NUM()

ROWNUM

Remark
  • The ROWNUM function specified in the WHERE clause works the same as the INST_NUM() function. Whereas INST_NUM() is a scalar function, GROUPBY_NUM() is a kind of an aggregate function. In a SELECT statement with a GROUP BY clause, GROUPBY_NUM() must be used instead of INST_NUM().
  • The ROWNUM function belongs to each SELECT statement. That is, if a ROWNUM function is used in a subquery, it returns the sequence of the subquery result while it is being executed. Internally, the result of the ROWNUM function is generated right before the searched record is written to the query result set. At this moment, the counter value that generates the serial number of the result set records increases.
  • If an ORDER BY clause is included in the SELECT statement, the value of the ROWNUM function specified in the WHERE clause is generated before sorting for the ORDER BY clause. If a GROUP BY clause is included in the SELECT statement, the value of the GROUPBY_NUM() function specified in the HAVING clause is calculated after the query results are grouped. After the sorting process is completed using the ORDER BY clause, you need to use the ORDERBY_NUM() function in the ORDER BY clause in order to get a sequence of the result records.
  • The ROWNUM function can also be used in SQL statements such as INSERT, DELETE and UPDATE in addition to the SELECT statement. For example, as in the query INSERT INTO table_name SELECT ... FROM ... WHERE ..., you can search for part of the row from one table and then insert it into another by using the ROWNUM function in the WHERE clause.
Example

The following example shows how to retrieve country names ranked first to fourth based on the number of gold (gold) medals in the 1988 Olympics in the demodb database.

--Limiting 4 rows using ROWNUM in the WHERE condition

SELECT  * FROM

(SELECT nation_code FROM participant WHERE host_year = 1988

     ORDER BY gold DESC) AS T

WHERE ROWNUM <5;

  nation_code

======================

  'URS'

  'GDR'

  'USA'

  'KOR'

 

--Limiting 4 rows using FOR ORDERBY_NUM()

SELECT ROWNUM, nation_code FROM participant WHERE host_year = 1988

ORDER BY gold DESC

FOR ORDERBY_NUM() < 5;

       rownum  nation_code

===================================

          156  'URS'

          155  'GDR'

          154  'USA'

          153  'KOR'

 

--Unexpected results : ROWNUM operated before ORDER BY

SELECT ROWNUM, nation_code FROM participant

WHERE host_year = 1988 AND ROWNUM < 5

ORDER BY gold DESC;

       rownum  nation_code

===================================

            1  'ZIM'

            2  'ZAM'

            3  'ZAI'

            4  'YMD'