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.
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.
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.
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.
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.
