Common mistake

SQL: Deduplicating Without ORDER BY Keeps the Wrong Row

ROW_NUMBER() with only PARTITION BY numbers rows in whatever order the engine reads them. Say which row should win, or the one you keep is an accident.

The symptom

You keep one session per user with the usual pattern: number the rows per user with row_number() and keep row 1. The result has exactly one row per user, the count is right, and it passes a glance. But you meant each user's latest session, and for two users it isn't.

Why it happens

row_number() over (partition by user_id) says how to group the rows, but not how to order them inside each group. SQL doesn't promise any order in that case, so the engine numbers them in whatever order it happens to read them. In this small table that's the order the rows were loaded, so user 2 keeps session 9 from January instead of session 13 from May.

Worse, "whatever order it reads them" can change. A bigger table, a parallel scan, or a new engine version can pick a different row on the next run, so the report changes although the data didn't.

The fix

Put the rule in the window: order by session_date desc makes the newest session row 1, and session_id desc settles two sessions on the same day.

Wrong: no ORDER BY in the window

with ranked as (
  select user_id,
         session_id,
         session_date,
         channel,
         row_number() over (partition by user_id) as rn
  from sessions
  where user_id is not null
)
select user_id, session_id, session_date, channel
from ranked
where rn = 1
order by user_id

Right: latest session wins

with ranked as (
  select user_id,
         session_id,
         session_date,
         channel,
         row_number() over (
           partition by user_id
           order by session_date desc, session_id desc
         ) as rn
  from sessions
  where user_id is not null
)
select user_id, session_id, session_date, channel
from ranked
where rn = 1
order by user_id

Nine of the eleven users come out the same, by luck. Users 2 and 7 differ: the unordered query keeps their first session, the ordered one their latest.

Decide what 'best' means first

Latest is only one rule. For duplicate customer records you might keep the most complete row, the one with a verified email, or the oldest account. Write that rule as the window's ORDER BY, and add a unique column last so there is never a tie.