{"id":95,"date":"2013-06-27T12:21:29","date_gmt":"2013-06-27T17:21:29","guid":{"rendered":"https:\/\/bijoos.com\/oraclenotes\/?p=95"},"modified":"2026-02-07T15:35:13","modified_gmt":"2026-02-07T21:35:13","slug":"oracledb12c-new-feature-new-row-limiting-clause-in-select","status":"publish","type":"post","link":"https:\/\/bijoos.com\/oraclenotes\/2013\/95\/","title":{"rendered":"OracleDB12c New Feature: New Row Limiting Clause in SELECT"},"content":{"rendered":"

Oracle Database 12c now includes a row_limiting_clause<\/strong> in the SELECT statement. This new clause limits the rows returned in a more meaningful and effective way, compared to the methods available in previous releases [using ROWNUM or using Top-N rows using subqueries or using analytic functions like RANK or DENSE_RANK].<\/p>\n

The syntax for the new clause according to the Oracle documentation<\/a> is:<\/p>\n

[ OFFSET offset { ROW | ROWS } ]\n[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]\n{ ROW | ROWS } { ONLY | WITH TIES } ]<\/pre>\n
the row_limiting_clause follows the ORDER BY clause in the SELECT statement, though ORDER BY is not mandatory, but is recommended for consistent results if you are looking for Top-N analysis.<\/p>\n
OFFSET clause is used to skip the specified number of rows before the limiting begins. If the offset is higher than the number of rows retrieved or is NULL, no rows are returned. ROW, ROWS keywords are there only for readability.<\/p>\n
FETCH clause can specify the number of rows to return or a percentage of rows to return. \u00a0FIRST, NEXT keywords can be used interchangeably and is for semantic clarity only.<\/p>\n
ONLY specifies to return exact number or percent of rows to return.<\/p>\n
WITH TIES specifies\u00a0to return all rows that have the same sort keys as the\u00a0last row of the row-limited result set (requires an ORDER BY clause).<\/p>\n
Examples:<\/strong><\/em><\/p>\n
SQL> select * from jobs;\n\nJOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY\n---------- ----------------------------------- ---------- ----------\nAD_PRES    President                                20080 40000\nAD_VP      Administration Vice President            15000 30000\nAD_ASST    Administration Assistant                  3000 6000\nFI_MGR     Finance Manager                           8200 16000\nFI_ACCOUNT Accountant                                4200 9000\nAC_MGR     Accounting Manager                        8200 16000\nAC_ACCOUNT Public Accountant                         4200 9000\nSA_MAN     Sales Manager                            10000 20080\nSA_REP     Sales Representative                      6000 12008\nPU_MAN     Purchasing Manager                        8000 15000\nPU_CLERK   Purchasing Clerk                          2500 5500\nST_MAN     Stock Manager                             5500 8500\nST_CLERK   Stock Clerk                               2008 5000\nSH_CLERK   Shipping Clerk                            2500 5500\nIT_PROG    Programmer                                4000 10000\nMK_MAN     Marketing Manager                         9000 15000\nMK_REP     Marketing Representative                  4000 9000\nHR_REP     Human Resources Representative            4000 9000\nPR_REP     Public Relations Representative           4500 10500\n\n19 rows selected.<\/pre>\n
 <\/p>\n
SQL> select * from jobs order by min_salary desc \n     fetch first 5 rows only;\n\nJOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY\n---------- ----------------------------------- ---------- ----------\nAD_PRES    President                                20080 40000\nAD_VP      Administration Vice President            15000 30000\nSA_MAN     Sales Manager                            10000 20080\nMK_MAN     Marketing Manager                         9000 15000\nFI_MGR     Finance Manager                           8200 16000\n\n5 rows selected.<\/pre>\n
 <\/p>\n
SQL> select * from jobs order by min_salary desc \n     fetch first 5 rows with ties;\n\nJOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY\n---------- ----------------------------------- ---------- ----------\nAD_PRES    President                                20080 40000\nAD_VP      Administration Vice President            15000 30000\nSA_MAN     Sales Manager                            10000 20080\nMK_MAN     Marketing Manager                         9000 15000\nFI_MGR     Finance Manager                           8200 16000\nAC_MGR     Accounting Manager                        8200 16000\n\n6 rows selected.<br><\/pre>\n
 <\/p>\n
SQL> select * from jobs order by min_salary desc \n     offset 3 rows fetch first 5 rows with ties;\n\nJOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY\n---------- ----------------------------------- ---------- ----------\nMK_MAN     Marketing Manager                         9000 15000\nFI_MGR     Finance Manager                           8200 16000\nAC_MGR     Accounting Manager                        8200 16000\nPU_MAN     Purchasing Manager                        8000 15000\nSA_REP     Sales Representative                      6000 12008\n\n5 rows selected.<br><\/pre>\n
 <\/p>\n
SQL> select * from jobs order by min_salary desc \n     fetch first 20 percent rows only;\n\nJOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY\n---------- ----------------------------------- ---------- ----------\nAD_PRES    President                                20080 40000\nAD_VP      Administration Vice President            15000 30000\nSA_MAN     Sales Manager                            10000 20080\nMK_MAN     Marketing Manager                         9000 15000\n\n4 rows selected.<\/pre>\n
 <\/p>\n
SQL> select * from jobs order by min_salary desc \n     offset 3 rows fetch first 20 percent rows with ties;\n\nJOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY\n---------- ----------------------------------- ---------- ----------\nMK_MAN     Marketing Manager                         9000 15000\nFI_MGR     Finance Manager                           8200 16000\nAC_MGR     Accounting Manager                        8200 16000\nPU_MAN     Purchasing Manager                        8000 15000\n\n4 rows selected.<\/pre>\n
 <\/p>\n
 <\/p>\n
\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle Database 12c now includes a row_limiting_clause in the SELECT statement. This new clause limits the rows returned in a more meaningful and effective way, compared to the methods available in previous releases [using ROWNUM or using Top-N rows using subqueries or using analytic functions like RANK or DENSE_RANK]. The syntax for the new clause … Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[133],"tags":[40],"class_list":["post-95","post","type-post","status-publish","format-standard","hentry","category-oracledb","tag-oracle-12c"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/95","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=95"}],"version-history":[{"count":1,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/95\/revisions"}],"predecessor-version":[{"id":3041,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/95\/revisions\/3041"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=95"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=95"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=95"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}