Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

GROUPBY_NUM() Function

Description

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.

Syntax

GROUPBY_NUM()

Example

The following example shows how to retrieve the fastest record in the previous five Olympic Games from the history table in the demodb database.

--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'