Earlier today, Ray Camden posted a quick ColdFusion-centric solution to showing only the Nth records of a query. His solution is fine, but I wonder how it would perform for large queries on a site under high load. Here's a database-centric solution that pulls back every Nth record, allowing CF to just display the final record set.

This example uses SQL Server 2005.

Step 1

There's a table in the database named "Districts". Let's get all the Districts for Texas.

view plain print about
1SELECT
2    Name
3FROM
4    Districts
5WHERE
6    state = 'TX'
7ORDER BY
8    Name

This brings back 1,063 rows.

Step 2

Let's use the ROW_NUMBER() function to get a record count within the results.

view plain print about
1SELECT
2    Name,
3    ROW_NUMBER() OVER ( ORDER BY Name ) AS 'RowNumber'
4FROM
5    Districts
6WHERE
7    state = 'TX'

Notice that the ORDER BY clause is no longer needed at the end of the query. It's now called from within ROW_NUMBER(). We still have 1,063 records, but now we can start manipulating the results.

Step 3

We'll go ahead and select * from the results we just returned (now aliased as EXPR1), but we'll also get the MOD of each RowNumber using 5 as the divisor.

view plain print about
1SELECT
2    Name,
3    RowNumber,
4    (EXPR1.RowNumber % 5) AS ROW_MOD
5FROM
6    (
7    SELECT
8        Name,
9        ROW_NUMBER() OVER ( ORDER BY Name ) AS 'RowNumber'
10    FROM
11        Districts
12    WHERE
13        state = 'TX'
14    ) EXPR1

Still 1,063 rows, but we're getting close.

Step 4

Now we just select * from the last record set (aliased as EXPR2) WHERE ROW_MOD = 0 and we're done.

view plain print about
1SELECT
2    EXPR2.*
3FROM
4    (
5    SELECT
6        Name,
7        RowNumber,
8        (EXPR1.RowNumber % 5) AS ROW_MOD
9    FROM
10        (
11        SELECT
12            Name,
13            ROW_NUMBER() OVER ( ORDER BY Name ) AS 'RowNumber'
14        FROM
15            Districts
16        WHERE
17            state = 'TX'
18        ) EXPR1
19    ) EXPR2
20WHERE
21    EXPR2.ROW_MOD = 0

Conclusion

The final result? 212 records where RowNumber MOD 5 = 0. This is every 5th row of the original query.

Traditional ColdFusion approach: Bring back 1,063 records from the database, then either (a, cfoutput) iterate over the entire record set, checking if the currentrow MOD 5 = 0 or (b, cfloop) iterate over the entire record set, stepping by 5.

ColdFusion with smart SQL approach: Bring back the 212 records we need and display them.

I know which way I prefer. :)

Related Links

Jules Gravinese has posted how to Select Every Nth Record with MySQL.