如何在SQL中基于ROW_NUMBER()为除最后一行外的行设置标记

Got it, let's break this down. You need to add a flag to every row in your events table except the final one, and you want to use ROW_NUMBER() to make it happen. Here's a solid, flexible approach:

Step 1: Define "Last Row" with a Sort Order #

First, we need to clarify what "last row" means—this almost always depends on a sortable column (like a timestamp, auto-increment ID, or sequence number). For this example, I'll assume you want the "last row" to be the most recent event, sorted by event_time descending. Adjust the sort column to match your actual data if needed.

Step 2: The Query #

SELECT
    *,
    -- Mark as 1 if it's NOT the last row, 0 if it is
    CASE
        WHEN row_num != total_rows THEN 1
        ELSE 0
    END AS is_not_last_row
FROM (
    -- Inner query to assign row numbers and get total row count
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY event_time DESC) AS row_num,
        COUNT(*) OVER () AS total_rows
    FROM events
) AS ranked_events;

How This Works #

• Inner Subquery:
  • ROW_NUMBER() OVER (ORDER BY event_time DESC) assigns a unique number to each row, starting at 1 for the most recent event.
  • COUNT(*) OVER () calculates the total number of rows in the events table—this is a window function that doesn't partition the data, so it returns the same count for every row.
• Outer Query:
  • The CASE statement compares the row number to the total row count. If they don't match, the row isn't the last one, so we set is_not_last_row to 1. If they do match, it's the final row, so we set it to 0.

Example Output #

Suppose your events table looks like this:

Running the query would give you:

Adjust for Your Sort Logic #

If your "last row" is based on a different column (like the highest id), just update the ORDER BY clause in the ROW_NUMBER() function. For example, to use the largest id as the last row:

ROW_NUMBER() OVER (ORDER BY id ASC) AS row_num

This way, the row with the highest id will have a row_num equal to total_rows, and get marked as 0.

内容的提问来源于stack exchange，提问作者Sam Bin Ham