How to Get First Row Per Group 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;
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and SQL Server