SQL Top Limit Fetch RowNum — Too Much Of A Good Thing

Relational databases store information in tables — tables with columns analogous to elements in a data structure and rows which are an instance of that data structure. We sometimes need to limit the number of returned rows. SQL Top, SQL Limit, SQL Fetch, and SQL RowNum (the exact syntax varies by database) are our tools in extracting the exact data we want.

In contrast to the usual behavior of the SQL Select statement, returning all relevant data, the limit function in SQL — expressed as one of Top, Limit, Fetch, or Rownum — provides a mechanism for limiting the data returned to either an absolute number or percentage of the rows. Limiting returned rows to bite-sized manageable chunks drastically reduces the storage and processing overhead requirements on software that consumes database data, resulting in a faster and more reliable code.

Rows are stored in databases at random. One adds certainty to which rows are retrieved by crafting the SQL Select statement with a winnowing WHERE clause and an ORDER BY clause. The techniques in this blog entry show how to retrieve a subset of those nicely-ordered rows, or how to iterate through those subsets across the entire results set.

As mentioned in the SQL Like Wildcard Searching, standards provide consistency for programmers. As there are no standards in place across database implementations for limiting data return, we’ll examine the different syntax for parallel functionality in MySQL, Microsoft SQL Server and Access, and several generations of Oracle databases.

MySQL Limit Number

The syntax of the MySQL Limit operator is:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

At the end of this blog post is the SQL required to create and populate a table of well-regarded dystopian novels. To select any three of these novels published before 1976 use the LIMIT clause:

SELECT title
FROM dystopias
WHERE year < 1976
LIMIT 3;

Which results in something which looks like the following table. (The specific rows returned depend entirely on the internal representation of the data stored in your database instance.)

SQL Server & MS Access Top Number or Percentage

The syntax of both Microsoft SQL Server and Microsoft Access, using the Select Top clause, is:

SELECT TOP number|percentage column_name(s)
FROM table_name
WHERE condition;

Which looks like the following in practice.

SELECT TOP 50 PERCENT * FROM dystopia;

Oracle — An Evolving Syntax

The technique and syntax of limiting rows returned in Oracle’s SQL has evolved over release versions.

Oracle Fetch First Number

Oracle version 12 uses the SQL Fetch First clause, and so looks like:

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;

This appears in SQL code as:

SELECT * FROM dystopia
FETCH FIRST 50 PERCENT ROWS ONLY;

Oracle RowNum

Because older versions of Oracle are plentiful, its syntax is worthwhile to have handy. To use the RowNum conditional clause, such that the requesting program can request exactly the number of rows returned for each call (and they can iterate over, say, a million records in a loop that handles a much smaller number each time). The SQL RowNum clause looks like:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Oracle RowNum With Order By

Another technique used by older Oracle instances is having a sub-select that orders the rows into a known sequence and processes them in chunks by the top-level select. (Remember that rows have no inherent order in a table — neither the order in which the data was added nor ordered by any column value — so they must be ordered for uses like this one.)

SELECT *
FROM (SELECT column_name(s)
FROM table_name
ORDER BY column_name(s))
WHERE ROWNUM <= number;

Techniques To Help With Retrieving Chunks Of Rows

Some of the above SQL statements allow retrieving rows in smaller chunks as one  iterates over the total dataset. Given the size of the data in each row, the amount of processing space on the computer, and the available bandwidth, the programmer determines the optimal amount of data to retrieve from the database for processing each time through the loop that processes all the rows. Reliable software will adapt to changing conditions, in this case the total number of rows stored in the table; these techniques help process those chunks.

The most straightforward ways to limit the number of rows returned are the modifier clauses shown above — Top, Limit, Fetch, and Rownum. There may be times when other programmatic ways of specifying limits is appropriate. The following techniques may then be used.

COUNT() the number of total rows

Writing adaptive software that bases retrieval decisions on the number of rows starts by determining the current table contents. The following gets the integer count of the rows.

SELECT COUNT(title)
FROM dystopias;

Break The Total Into Manageable Chunks

Let’s say it’s been determined that it’s appropriate to return one-third of the total number of rows. Combining COUNT() with the SQL FLOOR() function, which rounds fractional numbers to integers, and the ability to do math within SQL, the following will work (but not well, as we’ll see):

SELECT COUNT(title) / 3
FROM dystopias;

Because rows don’t come in fractional amounts — there’s no such thing as .3333 of a row — we must round down to the nearest integer. We do this with the SQL FLOOR() function:

SELECT ( FLOOR ( COUNT(title) / 3 ) )
FROM dystopias;

Requesting 3 records makes sense in SQL, and we’re good to go. (Note that CEILING() is the counterpart function, rounding up to the nearest whole number.)

This kind of defensive programming becomes reflexive with experience, so it’s worthwhile to cover such things in this blog. Thinking ahead and armor-plating code pays for itself in the long run by lowering software debugging time and the related maintenance costs. Anytime you’re doing calculations in SOL keep in mind that the result may be fractional, and use FLOOR() or CEILING() before problems arise.

Conclusion

Limiting the number of rows returned from a SQL Select statement may be necessary. We’ve covered both the varying syntax of SQL clauses — Top, Limit, Fetch, and Rownum — as they appear in several popular database and, subsequently, several additional programming techniques helpful when calculating a number of rows to handle through an iterative loop.

Further readings include a deeper Udacity SQL Language Tutorial a topic-by-topic list of Udacity Hub of SQL Language Topics.

Start Learning

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

SQL Sample Table Code

The following will build the code sample tables referred to in this blog post. Examine them interactively at  SQL Fiddle or CodingGround.

CREATE TABLE dystopias (
titleVARCHAR(33),
yearDECIMAL,
authorVARCHAR(50)
);
INSERT INTO dystopias
VALUES
( "The Giver", 1993, "Lois Lowry" ),
( "A Clockwork Orange", 1962, "Anthony Burgess" ),
( "We", 1921, "Yevgeni Zamiatin" ),
( "The Children of Men", 1992, "P.D. James" ),
( "Fahrenheit 451", 1953, "Ray Bradbury" ),
( "The Iron Heel", 1908, "Jack London" ),
( "Gulliver’s Travels", 1726, "Jonathan Swift" ),
( "The Handmaid’s Tale", 1985, "Margaret Atwood" ),
( "Nineteen Eighty-Four", 1949, "George Orwell" ),
( "Brave New World", 1932, "Aldous Huxley" );
-- Source: www.strangelist.com/top-10-famous-utopias-dystopias/