Search code examples
sqlpostgresqlwindow-functions

Rolling numbering of data in a view

I am using the following query to number the data based on parameter a and b.

count(table.a) OVER (PARTITION BY table.b, tdahw.a ORDER BY table.date) AS row_count

The output is numbering the values as incremental. However, if there are 3 counts of a over partition of a and b, I want it to display 3 across all the rows. What tweaks should I make to the existing thing?

Sample data:

enter image description here

Solution

  • Just remove order by :

    select count(m.a) OVER (PARTITION BY m.b, m.a) AS row_count 
from mytable m

    Here is a simple demo I created:

    DEMO

    So for you it would be :

    count(table.a) OVER (PARTITION BY table.b, tdahw.a) AS row_count