Automatically Grouping Query Results into Row Sets of a Specific Size

I came up with a simple formula a couple of days ago that will automatically determine which result set  a row belongs in if the results of the query need to be grouped in specific numeric sets of rows (such as sets of five rows, or sets of 10 rows, etc ..) for retrieval and display in an object such as a table widget on a web page.  These would be the sets of rows that are displayed by the ‘Next’, ‘Last’, and ‘Previous’ buttons (or arrows) on the widget.  The formula can  be applied directly in the query, or separately as a function (depends on how you are handling your query results before passing them to the widget).  Here is the formula:

Rowset# = (trunc((x-1)/y)+1) where x is equal to the selected row number and y is the number of rows per set.

If we apply this formula in a query where want the resulting rows to be grouped into sets of five (5), it would look something like this (sample query uses the standard Oracle demo EMPLOYEES table) :

As you can see, the formula is simple and (best of all)  it works.   As always, feel to shoot me an email if you have questions.

Comments are closed.