****************
ROWNUM Functions
****************
ROWNUM, INST_NUM
================
.. c:macro:: ROWNUM
.. function:: INST_NUM ()
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.
:rtype: INT
**ROWNUM** and **INST_NUM()** can be used in the **SELECT** statement; **ORDERBY_NUM()** can be used in the **SELECT** statement with **ORDER BY** clauses, 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.
.. note::
* The **ROWNUM** function specified in the **WHERE** clause works the same as the **INST_NUM()** function.
* 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 you want to add a sequence on the **SELECT** result; use **ROWNUM** when there is no sorting process; use **ORDERBY_NUM()** when there is an ORDER BY clause; use **GROUPBY_NUM()** function when there is a GROUP BY clause.
* If an **ORDER BY** clause is included in the **SELECT** statement, the value of the **ORDERBY_NUM()** function specified in the **WHERE** clause is generated after 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.
* For the purpose of limiting the sorted result rows, instead of **FOR ORDERBY_NUM()** or **HAVING GROUPBY_NUM()**, **LIMIT** clause can be used.
* 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.
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.
.. code-block:: sql
--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'
LIMIT clause limits the sorted result rows; therefore, the below result is the same as the above.
.. code-block:: sql
--Limiting 4 rows using LIMIT
SELECT ROWNUM, nation_code FROM participant WHERE host_year = 1988
ORDER BY gold DESC
LIMIT 4;
::
rownum nation_code
===================================
156 'URS'
155 'GDR'
154 'USA'
153 'KOR'
ROWNUM condition in the below limits before sorting; therefore, the result is different from the above.
.. code-block:: sql
--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'
ORDERBY_NUM
===========
.. function:: ORDERBY_NUM ()
The **ORDERBY_NUM()** function is used with the **ROWNUM()** or **INST_NUM()** function to limit the number of result rows. The difference is that the **ORDERBY_NUM()** function is combined after the ORDER BY clause to give order to a result that has been already sorted. That is, when retrieving only some of the result rows by using **ROWNUM** in a condition clause of the **SELECT** statement that includes the **ORDER BY** clause, **ROWNUM** is applied first and then group sorting by **ORDER BY** is performed. On the other hand, when retrieving only some of the result rows by using the **ORDER_NUM()** function, **ROWNUM** is applied to the result of sorting by **ORDER BY**.
:rtype: INT
The following example shows how to retrieve athlete names ranked 3rd to 5th and their records in the *history* table in the *demodb* database.
.. code-block:: sql
--Ordering first and then limiting rows using FOR ORDERBY_NUM()
SELECT ORDERBY_NUM(), athlete, score FROM history
ORDER BY score FOR ORDERBY_NUM() BETWEEN 3 AND 5;
::
orderby_num() athlete score
==============================================================
3 'Luo Xuejuan' '01:07.0'
4 'Rodal Vebjorn' '01:43.0'
5 'Thorpe Ian' '01:45.0'
The following query using a LIMIT clause outputs the same result with the above query.
.. code-block:: sql
SELECT ORDERBY_NUM(), athlete, score FROM history
ORDER BY score LIMIT 2, 3;
The following query using ROWNUM limits the result rows before sorting; then ORDER BY sorting is operated.
.. code-block:: sql
--Limiting rows first and then Ordering using ROWNUM
SELECT athlete, score FROM history
WHERE ROWNUM BETWEEN 3 AND 5 ORDER BY score;
::
athlete score
============================================
'Thorpe Ian' '01:45.0'
'Thorpe Ian' '03:41.0'
'Hackett Grant' '14:43.0'
GROUPBY_NUM
===========
.. function:: GROUPBY_NUM ()
The **GROUPBY_NUM()** function is used with the **ROWNUM** or **INST_NUM()** function to limit the number of result rows. The difference is that the **GROUPBY_NUM()** function is combined after the **GROUP BY ... HAVING** clause to give order to a result that has been already sorted. In addition, while the **INST_NUM()** function is a scalar function, the **GROUPBY_NUM()** function is kind of an aggregate function.
That is, when retrieving only some of the result rows by using **ROWNUM** in a condition clause of the **SELECT** statement that includes the **GROUP BY** clause, **ROWNUM** is applied first and then group sorting by **GROUP BY** is performed. On the other hand, when retrieving only some of the result rows by using the **GROUPBY_NUM()** function, **ROWNUM** is applied to the result of group sorting by **GROUP BY**.
:rtype: INT
The following example shows how to retrieve the fastest record in the previous five Olympic Games from the *history* table in the *demodb* database.
.. code-block:: sql
--Group-ordering first and then limiting rows using GROUPBY_NUM()
SELECT GROUPBY_NUM(), host_year, MIN(score) FROM history
GROUP BY host_year HAVING GROUPBY_NUM() BETWEEN 1 AND 5;
::
groupby_num() host_year min(score)
=====================================================
1 1968 '8.9'
2 1980 '01:53.0'
3 1984 '13:06.0'
4 1988 '01:58.0'
5 1992 '02:07.0'
The following query using a LIMIT clause outputs the same result with the above query.
.. code-block:: sql
SELECT GROUPBY_NUM(), host_year, MIN(score) FROM history
GROUP BY host_year LIMIT 5;
The following query using ROWNUM limits the result rows before grouping; then GROUP BY operation is performed.
.. code-block:: sql
--Limiting rows first and then Group-ordering using ROWNUM
SELECT host_year, MIN(score) FROM history
WHERE ROWNUM BETWEEN 1 AND 5 GROUP BY host_year;
::
host_year min(score)
===================================
2000 '03:41.0'
2004 '01:45.0'
They went down to the water-side to try the effects of a bath in the surf as it rolled in from the Pacific Ocean. They found it refreshing, and were tempted to linger long in the foam-crested waves. Near by there was a fishing-place, where several Japanese were amusing themselves with rod and line, just as American boys and men take pleasure in the same way. Fish seemed to be abundant, as they were biting freely, and it took but a short time to fill a basket. In the little harbor formed between the island and the shore several junks and boats were at anchor, and in the foreground some smaller boats were moving about. There was not an American feature to the scene, and the boys were thoroughly delighted at this perfect picture of Japanese life. It was sea-life, too; and they had island and main, water and mountain, boats and houses, all in a single glance. "For our sick soldiers!" "Yes, I'm going to take that away with me to-day." "I destroyed it. There was no object in keeping it. I tore it up then and there and pitched it on the pavement. The motor was driven by a dumb man, who conveyed me to the corner house. It struck me as strange, but then the owner might have returned. When I got there I found the man subsequently murdered suffering from a combination of alcoholic poisoning and laudanum. It was hard work, but I managed to save him. A Spanish woman--the only creature besides my patient I saw--paid me a fee of three guineas, and there ends the matter." fatherly letter--but frank! He said he saw from the address that I ¡°Who are you, please?¡± Sandy shot the question out suddenly. He had gone back. "You'll come through all right," said the Surgeon smiling. "You're the right kind to live. You've got grit. I'll look at your partner now." 'he Took Another Look at his Heavy Revolver.' 254 But after the gun was gone, and after Shorty had written a laborious letter, informing Sammy of the shipment of the gun and its history, which letter inclosed a crisp greenback, and was almost as urgent in injunctions to Sammy to write as Sammy had been about his piece of ordnance, Shorty sat down in sadness of heart. He was famishing for information from Maria, and at the lowest calculation he could not hope for a letter from Sammy for two weeks. The firing and stone-throwing lasted an hour or more, and then seemed to die down from sheer exhaustion. Odiam had triumphed at last. Just when Reuben's unsettled allegiance should have been given entirely to the wife who had borne him a son, his farm had suddenly snatched from him all his thought, all his care, his love, and his anxiety, all that should have been hers. It seemed almost as if some malignant spirit had controlled events, and for Rose's stroke prepared a counter-stroke that should effectually drive her off the field. The same evening that Rose had gone weeping and shuddering upstairs, Reuben had interviewed the vet. from Rye and heard him say "excema epizootica." This had not conveyed much, so the vet. had translated brutally: "I don't ask for that to-night¡ªall I ask is food and shelter, same as you'd give to a dog." "Yes, yes, we will consider of some more fitting answer," said Leicester fiercely;¡ªand after consulting earnestly for a few minutes with Jack Straw, Thomas Sack, and other leaders, he returned to De Vere, and said¡ª HoMEÃâ·ÑÍøÕ¾Ò»¼¶ÊÓƵ²¤ÂÜÃÛ
ENTER NUMBET 0018zhengfei.net.cn
www.sanfangzhifu.com.cn
www.jinchenmc.com.cn
www.pabaj.com.cn
77ebuy.com.cn
fxhe.com.cn
www.seage.com.cn
ysjh.net.cn
www.glonics.com.cn
ag8.org.cn