Announcing our $3.4M seed round 🚀 Read more →

How to Get First Row Per Group in SQL Server in SQL Server

Let's say you have tables that contains data about users and sessions, and you want to see the first session for each user for particular day. The function you need here is row_number(). Here's an example of how to use it:

select
  us.user_id,
  us.session_id,
  s.start_date,
  s.end_date,
  row_number() over (partition by user_id order by start_date desc) as row_number
from user_sessions us
left outer join sessions s on s.id = us.session_id
where convert(varchar(10), start_date, 105) = '02-02-2020';

This gives you all the session IDs for the day, plus their row number. Since you only want the first session for the day, you only want rows that have row_number: 1. To do that, you can use a common table expression:

with cte_sessions as (
  select
    us.user_id,
    us.session_id,
    s.start_date,
    s.end_date,
    row_number() over (partition by user_id order by start_date desc) as row_number
  from user_sessions us
  left outer join sessions s on s.id = us.session_id
  where convert(varchar(10), start_date, 105) = '02-02-2020'
)
select *
from cte_sessions
where row_number = 1;

Ready for a modern SQL editor?