SQL SELECT Nth Record

Transact-SQL (TSQL) Quick Tips – SQL SELECT Nth Record

I recently needed to compare two month end data snapshots.  The data was organized in a historical table with monthly datasets, dating back several years.  The specific need was to query changes between the previous dataset and the most recent.  The task itself isn’t difficult.  What took a little time to figure out was dynamically setting the “Previous Month” variable for use in the query.

In this post, I’m going to show a couple examples of how to SELECT the Nth record in a given dataset.

Example 1 – Query the “Previous” month end date from a monthly dates table

Executing the SQL Query above returns the following:

SQL SELECT Nth Record

Using the ROW_NUMBER Ranking Function, each date is “Ranked” in descending order by PeriodDate field.  By selecting the “RowDate” equal to two, you have the ability to pick and choose which date you would like to use in further processing.  I often use this technique to declare date variables, for the current / previous month, then use them to query month over month comparisons.

Example 2 – Query the “Previous” quarter (rolling three months) from a monthly dates table

Executing the SQL Query above returns the following:

SQL SELECT Nth Record

Again, each date is “Ranked” in descending order by PeriodDate field.  By selecting the “RowDate” less than four, the most-recent three month end dates will be returned.

Example 3 – Top 10 List

Executing the SQL Query above returns the following:

SQL SELECT Nth Record

Using the ROW_NUMBER Ranking Function, each team’s “PointsPerGame” is ranked in descending order.  By selecting “PointsRank” less than 11, the top 10 ranked teams will be returned.  This is very useful when users are looking for lists of the highest ranking items for a given attribute.

 

If you would like to post any additional examples, please add them to the comments below.

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

Leave a Comment