sqlcmd.net validated sql reference

Command guide

advanced windowing MySQL MariaDB SQL Server PostgreSQL SQLite

Group Consecutive Rows With A ROW_NUMBER Difference

Create stable run groups without a primary key by subtracting two `ROW_NUMBER()` calculations over the same ordered events.

5/5 supported engines validation-green 1 example 2 scenarios
Docker-validated Not currently validation-green

Example 1

Create a run group id for consecutive status rows ordered by timestamp

Within each account, the first ROW_NUMBER() counts every event in timestamp order, while the second counts only rows within the same status. Their difference stays constant for each consecutive run of the same status. The important caveat is that group_offset alone is not the full identifier when a status value appears again later, so the practical run key is (status, group_offset) or the derived group_id. If two rows can share the same timestamp, add another stable tie-breaker to the ORDER BY so the sequence remains deterministic.

Source table data Rows loaded before the example query runs.
Setup
CREATE TABLE device_events (
  account VARCHAR(20),
  event_timestamp DATETIME,
  status VARCHAR(20)
);

INSERT INTO
  device_events (account, event_timestamp, status)
VALUES
  ('alpha', '2026-04-01 09:00:00', 'online'),
  ('alpha', '2026-04-01 09:05:00', 'online'),
  ('alpha', '2026-04-01 09:10:00', 'offline'),
  ('alpha', '2026-04-01 09:15:00', 'offline'),
  ('alpha', '2026-04-01 09:20:00', 'online'),
  ('beta', '2026-04-01 10:00:00', 'online'),
  ('beta', '2026-04-01 10:05:00', 'offline'),
  ('beta', '2026-04-01 10:10:00', 'offline'),
  ('beta', '2026-04-01 10:15:00', 'online');
Validated query Shared across supported engines.
SQL
WITH
  numbered AS (
    SELECT
      account,
      event_timestamp,
      status,
      ROW_NUMBER() OVER (
        PARTITION BY
          account
        ORDER BY
          event_timestamp
      ) - ROW_NUMBER() OVER (
        PARTITION BY
          account,
          status
        ORDER BY
          event_timestamp
      ) AS group_offset
    FROM
      device_events
  )
SELECT
  account,
  event_timestamp,
  status,
  group_offset,
  CONCAT (status, '-', group_offset) AS group_id
FROM
  numbered
ORDER BY
  account,
  event_timestamp;
Expected result Returned rows for the shared example.
accountevent_timestampstatusgroup_offsetgroup_id
alpha2026-04-01 09:00:00online0online-0
alpha2026-04-01 09:05:00online0online-0
alpha2026-04-01 09:10:00offline2offline-2
alpha2026-04-01 09:15:00offline2offline-2
alpha2026-04-01 09:20:00online2online-2
beta2026-04-01 10:00:00online0online-0
beta2026-04-01 10:05:00offline1offline-1
beta2026-04-01 10:10:00offline1offline-1
beta2026-04-01 10:15:00online2online-2

The example uses timestamp ordering to derive consecutive-run groups consistently across all supported engines.

Useful when

Where this command helps.

  • grouping consecutive status runs in event history tables that have no primary key
  • assigning run ids to ordered rows before summarizing islands of the same value

Explanation

What the command is doing.

Sometimes a table has no primary key or other natural unique key, but the rows still have a meaningful event sequence. A useful gaps-and-islands pattern is to subtract ROW_NUMBER() OVER (PARTITION BY something1 ORDER BY somethingelse) from ROW_NUMBER() OVER (PARTITION BY something1, something2 ORDER BY somethingelse). The difference stays constant for consecutive runs of the same something2 value within each something1. In practice, use the repeated value together with that difference as the group identifier.

Tags

window-functionsrow-numbergaps-and-islandsgroupingpitfall

Related commands

Keep moving through nearby tasks.