Search code examples
sqldatabaseforeign-keysreporting

SQL - Counting up - by foreign key

I'm trying to create a report that pulls data with an extra field that increases by the instances of a foreign key, IE; When person 1 appears in the list for the first time, this field pulls the number 1, on the 15th appearance, it'll be 15 etc.

I feel as if there's possibly a way with the Partition function, however i can seem to get the syntax through my head!

if it's not possible, fair enough as it technically can be done in Excel, however automation is the key!

Thanks all!

Jack

Solution

  • I think you want row_number():

    select t.*,
       row_number() over (partition by person order by <ordering column>) as seqnum
from t;