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.
GROUPBY_NUM()
The following example shows how to retrieve the fastest record in the previous five Olympic Games from the history table (demodb).
--Group-ordering first and then limiting rows using GROUPBY_NUM()
SELECT host_year, MIN(score) FROM history
GROUP BY host_year HAVING GROUPBY_NUM() BETWEEN 1 AND 5;
host_year min(score)
===================================
1968 '8.9'
1980 '01:53.0'
1984 '13:06.0'
1988 '01:58.0'
1992 '02:07.0'
--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'