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.

Step 1

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.

SQL query

Step 2

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.

SQL query with Row Number

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.

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.

SQL query with ROW_MOD

Step 4

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

Final SQL query

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.

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.

About the Author
Adrian J. Moreno

Adrian is an enterprise solution architect and full stack developer. Which stack depends on which system is on fire at the time. More information