SQL approach to Showing Every Nth Record
Posted on February 20, 2009
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 itwould 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.
There’s a table in the database named “Districts”. Let’s get all the Districts for Texas.
SELECT Name FROM Districts WHERE state = 'TX' ORDER BY Name
This brings back 1,063 rows.
Let’s use the ROW_NUMBER() function to get a record count within the results.
SELECT Name, ROW_NUMBER() OVER ( ORDER BY Name ) AS 'RowNumber' FROM Districts WHERE 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.
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.
SELECT Name, RowNumber, (EXPR1.RowNumber % 5) AS ROW_MOD FROM ( SELECT Name, ROW_NUMBER() OVER ( ORDER BY Name ) AS 'RowNumber' FROM Districts WHERE state = 'TX' ) EXPR1
Still 1,063 rows, but we’re getting close.
Now we just select * from the last record set (aliased as EXPR2) WHERE ROW_MOD = 0 and we’re done.
SELECT EXPR2.* FROM ( SELECT Name, RowNumber, (EXPR1.RowNumber % 5) AS ROW_MOD FROM ( SELECT Name, ROW_NUMBER() OVER ( ORDER BY Name ) AS 'RowNumber' FROM Districts WHERE state = 'TX' ) EXPR1 ) EXPR2 WHERE EXPR2.ROW_MOD = 0
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.
Smart SQL approach: Bring back the 212 records we need and display them.
I know which way I prefer. :)
Jules Gravinese has posted how to Select Every Nth Record with MySQL.
Adrian J. Moreno
Adrian is an enterprise solution architect and full stack developer. Just which stack depends on what system is on fire at the time. More information