SQL for adding another column showing Nth occurrence of unique ID within data-set

I have a table of patient visits, where each row is a visit. There is a column with the unique patient ID to signify which client the visit was for. I would like to add another column which shows the Nth occurrence of that patient ID within the data-set. So first visit would show 1, second visit shows 2 and so on. I tried searching for similar questions but have not found anything yet, your help would be greatly appreciated!

Answer

You can enumerate the values using row_number():

select v.*,
       row_number() over (partition by patient_id order by visit_date) as seqnum
from visits v;

This is called a window function (or more specifically a ranking function). It enumerates the rows without gaps starting at 1 for each “partition” (i.e. each patient_id). The order by specifies the ordering.