How can I get the observation number for each observation date for each id

Question

With the following query I can get the number of observations for each ani_id in a table:

select ani_id, obs_date, count(ani_id) OVER (PARTITION BY ani_id) as obs_nb 
from v_metadata_all group by ani_id, cap_date

ani_id	obs_date	obs_nb
2855	2005-06-15	1
2856	2005-06-15	1
2857	2005-06-15	2
2857	2009-08-28	2
2858	2005-08-11	1

How can I get obs_nb as the sum of previous obs for each ani_id (ani_id could have between 1 and 5 observations in this table, not only 2)

ani_id	obs_date	obs_nb
2855	2005-06-15	1
2856	2005-06-15	1
2857	2005-06-15	1
2857	2009-08-28	2
2858	2005-08-11	1

thank you

Answer 1

I think you want to use ROW_NUMBER in this case, not COUNT:

SELECT ani_id, obs_date,
       ROW_NUMBER() OVER (PARTITION BY ani_id ORDER BY obs_date) AS obs_nb
FROM v_metadata_all;