SQL approach to Showing Every Nth Record

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.

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.

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.

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

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

Related Links

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