Oracle Database 12c: Top-n Query

Hello readers!

Oracle Database 12c brings a new feature that enables you to easily limit the result set to a specific number or percentage of rows. For me this feature it is very interesting and does my job more easy when I need to develope some query to the clients

with needing to restrict the number of rows. I was waiting to a feature like this :D!!

Using the row_limiting clause to bring the output from some query based on a specific row offset. This can be possible using the offset n row this option from the row_limiting clause, below an example using this feature:

SQL> SELECT COUNT(*) FROM PRODUCT;

COUNT(*)
———-
20

SQL> SELECT PROD_NAME, PROMO_DATE FROM PRODUCT ORDER BY PROMO_DATE OFFSET 8 ROWS;

PROD_NAME            PROMO_DAT
————–               ———
KNIFE-GRINDER     09-AUG-12
SLIPPER                    29-JUL-13
TRINKET BOX         28-JAN-14
TV SHARP                02-MAR-14
CORKSCREW          31-JAN-15
NECKLACE              02-FEB-15
WIRE                         19-FEB-15
WARDROBE             30-MAR-15
HANDLE                   12-APR-15
BED                           14-MAY-15
RAINCOAT               02-JUL-15
LAMPSHADE            12-JUL-15

12 rows selected.

And you can also use the percent option of the row_limiting clause to fetch a certain percentage of rows:

SQL> SELECT PROD_NAME, PROMO_DATE FROM PRODUCT
          ORDER BY PROMO_DATE FETCH FIRST 25 PERCENT ROWS ONLY;

PROD_NAME      PROMO_DAT
————–         ———
STOVE                 01-MAY-09
CARPET               04-DEC-09
WALLET              23-JUL-11
PADLOCK           14-OCT-11
STEWPOT            22-NOV-11

5 rows selected.

You would like to know the next 25 percent of rows?

Let’s see:

SQL> SELECT PROD_NAME, PROMO_DATE FROM PRODUCT ORDER BY PROMO_DATE                   FETCH  NEXT 25 PERCENT ROWS ONLY;

PROD_NAME     PROMO_DAT
——————–      ———
STOVE                  01-MAY-09
CARPET               04-DEC-09
WALLET              23-JUL-11
PADLOCK           14-OCT-11
STEWPOT            22-NOV-11

Probably, you will see the same result for both queries. If you loading thousands of rows, you might not see the same output for the 25 percent every time. Because the PROMO_DATE is not unique. If you do not order by something unique your query is not deterministic using row_limiting clause.

To have a different result using the next 25 percent,  you have to include the offset value in the query as seen here:

SQL> SELECT PROD_NAME, PROMO_DATE FROM PRODUCT ORDER BY PROMO_DATE                   OFFSET 5 ROWS FETCH 2 NEXT 25 PERCENT ROWS ONLY;

PROD_NAME          PROMO_DAT
——————-       –     ———
PRINTER                   03-FEB-12
DOOR                         03-FEB-12
COOLER                     03-AUG-12
KNIFE-GRINDER     09-AUG-12
SLIPPER                     29-JUL-13
TRINKET BOX         28-JAN-14

6 rows selected.

Well, we have too the clause with ties to return additional rows with the same key as the last row fetched.

Let’s see the difference:

SQL> SELECT PROD_NAME, PRICE FROM PRODUCT ORDER BY PRICE FETCH FIRST 44 PERCENT ROWS ONLY;

PROD_NAME         PRICE
——————–         ———-
WALLET                  200
PADLOCK               200
STEWPOT               200
STOVE                     999
CARPET                   999
TV SHARP               1500
TRINKET BOX       1500
SLIPPER                  3712
PRINTER                 3972

9 rows selected.

Now using the clause with ties:

SQL> SELECT PROD_NAME, PRICE FROM PRODUCT ORDER BY PRICE FETCH FIRST 44 PERCENT ROWS WITH TIES;

PROD_NAME     PRICE
——————–    ———-
WALLET                 200
PADLOCK              200
STEWPOT               200
STOVE                     999
CARPET                   999
TV SHARP               1500
TRINKET BOX       1500
SLIPPER                  3712
PRINTER                 3972
DOOR                       3972
COOLER                   3972
KNIFE-GRINDER   3972

12 rows selected.

Because WITH TIES it is specified, the statement above returns the 44 percent of products with the lowest prices, plus all additional products with the same price as the last row fetched in the previous example.

These features are very useful, I saw in many forums DBAs and developers asking for features like this.

I hope you enjoy!

Cheers Felipe!