Hack 72. Extract a Subset of the Results

You have an ordered table, and you want to see only the first 10, 100, or 1,000 results. Here's how to get the chunks you want.

Suppose you have a high-score table to display. The table itself has thousands of entries, but you want only the first 10. For testing purposes you have the following:

CREATE TABLE highscore (username VARCHAR(20),score INT); INSERT INTO highscore VALUES ('gordon',10); INSERT INTO highscore VALUES ('user01',20); ... INSERT INTO highscore VALUES ('user11',120);

To get the top 10, you might write the following PHP:

9) {break;} print "Position ".$i.", ".$line{username}.", ".$line{score}." "; } ?>

The example output would be:

[gordon@db book]$ php -q ./do.php Position 1, user11, 120 Position 2, user10, 110 Position 3, user09, 100 Position 4, user08, 90 Position 5, user07, 80 Position 6, user06, 70 Position 7, user05, 60 Position 8, user04, 50 Position 9, user03, 40 Position 10, user02, 30

Although this works, you should be worried about caching and unnecessary data processing. The database system may be churning through thousands of records, getting ready to pass all the result rows to your program (it has no way of knowing that the application will stop requesting data after 10 rows). Your database drivers may be caching thousands of records, ready for you to ask for the next row. If you want only 10 records, all this memory and CPU effort are going to waste.

You can sometimes let the query optimizer know you want initial rows quickly. In Oracle, you can change the optimizer behavior for the duration of your opened database connection handle using ALTER SESSION:

ALTER SESSION SET optimizer_goal=first_rows_10

Per-query changes may also be possible. For instance, in Oracle you can introduce optimizer hints using special comments:

SELECT /*+ first_rows(10) */ username,score FROM highscore ORDER BY score DESC

In SQL Server, you can use the hint OPTION(FAST 10) to instruct the optimizer to return the first 10 rows as fast as possible, even if this impacts the time taken to return the whole result set:

SELECT username,score FROM highscore ORDER BY score DESC OPTION (FAST 10)

Neither of these changes stops the query from returning more than 10 rows. They only ask the database system to try to get the first 10 rows worked out quickly. So, you also need to specify that the SQL should filter out all but the first 10 rows.

Oracle numbers each row for you automatically in a query. The number of the row is called rownum:

SELECT username,score FROM (SELECT username,score FROM highscore ORDER BY score DESC) t WHERE rownum <= 10

SQL Server and Access allow the TOP keyword:

SELECT TOP 10 username,score FROM highscore ORDER BY score DESC

MySQL and PostgreSQL use the LIMIT instruction:

SELECT username,score FROM highscore ORDER BY score DESC LIMIT 10

9.9.1. Hacking the Hack

If you were querying lots of rows, but wanted to query them out of the database only 10 rows at a time, you could use the preceding techniques to get the first 10 rows. But how would you get the next 10 rows?

Oracle needs no special syntax to support this type of query, as you can do it with rownum. Unfortunately, rownum is calculated before ORDER BY, and the more obvious WHERE rownum >10 AND rownum <=20 never returns any rows. You can still use rownum, but you need three SELECT statements:

SELECT username,score FROM (SELECT rownum rnum,username,score FROM (SELECT username,score FROM highscore ORDER BY score DESC) ) WHERE rnum >10 and rnum <=20

Both MySQL and PostgreSQL support the OFFSET instruction:

SELECT username,score FROM highscore ORDER BY score DESC LIMIT 10 OFFSET 10

SQL Server does not support OFFSET. It is possible to emulate this in a number of different ways. For instance:

SELECT TOP 10 username,score FROM highscore WHERE username not in (SELECT TOP 10 username FROM highscore ORDER BY score DESC) ORDER BY score DESC