Retrieving the Last N Rows in SQL Server

Introduction

Frequently, when working with databases, you’ll need to retrieve only the most recent entries – the last N rows added to a table. This is common in scenarios like displaying recent activity, fetching the latest sensor readings, or showing the newest products. This tutorial focuses on how to efficiently retrieve the last N rows in SQL Server.

The Basic Approach: TOP and ORDER BY

The most straightforward method to retrieve the last N rows involves using the TOP keyword in conjunction with an ORDER BY clause.

SELECT TOP (N) *
FROM YourTable
ORDER BY YourPrimaryKeyColumn DESC;

Explanation:

• SELECT TOP (N): This limits the result set to the top N rows. Replace N with the number of rows you wish to retrieve.
• FROM YourTable: Specifies the table from which to retrieve the data.
• ORDER BY YourPrimaryKeyColumn DESC: This sorts the table in descending order based on your primary key (or a timestamp column representing the order of insertion). Descending order ensures that the most recently added rows appear at the top.

Example:

To retrieve the last 5 rows from a table named Products ordered by the ProductID (assuming it’s the primary key):

SELECT TOP (5) *
FROM Products
ORDER BY ProductID DESC;

Performance Considerations

While the TOP and ORDER BY approach is simple, it can be inefficient for large tables. Sorting the entire table to retrieve only the last N rows can be time-consuming. The performance impact is heavily dependent on whether an index exists on the column you’re ordering by.

• With an Index: If you have an index on YourPrimaryKeyColumn, SQL Server can efficiently locate the last N rows without sorting the entire table.
• Without an Index: Without an index, SQL Server must perform a full table scan and sort all rows, leading to significant performance degradation.

Using ROW_NUMBER() for More Control

For more complex scenarios, or when you need to partition the results, the ROW_NUMBER() window function provides a powerful alternative.

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY YourPrimaryKeyColumn DESC) AS RowNum
    FROM YourTable
) AS Subquery
WHERE RowNum <= N;

Explanation:

1. Inner Query (Subquery):
   • ROW_NUMBER() OVER (ORDER BY YourPrimaryKeyColumn DESC): This assigns a unique sequential integer to each row in the table, ordered by YourPrimaryKeyColumn in descending order. The most recent row receives the value 1, the next most recent 2, and so on.
2. Outer Query:
   • WHERE RowNum <= N: This filters the results, selecting only the rows where the assigned row number is less than or equal to N.

Example:

To retrieve the last 5 rows from a table named Orders ordered by the OrderDate:

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowNum
    FROM Orders
) AS Subquery
WHERE RowNum <= 5;

Partitioning with ROW_NUMBER()

The ROW_NUMBER() function can also be used to partition the results, allowing you to retrieve the last N rows within each group. For example, to retrieve the last 2 orders placed by each customer:

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowNum
    FROM Orders
) AS Subquery
WHERE RowNum <= 2;

Alternative Approaches (Less Common)

While the TOP and ROW_NUMBER() methods are generally the most efficient, other approaches exist. However, these are often less performant or more complex.

• EXCEPT Clause: This involves selecting all rows and then subtracting the first (total rows - N) rows. This is generally inefficient.
• Subqueries: Using a subquery to find the maximum primary key value and then selecting rows with a primary key less than that value can be slow for large tables.

Choosing the Right Approach

• For simple scenarios where you need to retrieve the last N rows from an entire table, the TOP and ORDER BY approach is usually the most efficient, provided you have an index on the ordering column.
• For more complex scenarios involving partitioning or filtering, the ROW_NUMBER() function offers greater flexibility and control.
• Avoid using EXCEPT or complex subqueries unless you have a specific reason to do so, as they can often lead to performance issues.