Counting rows for a particular name

Question

I've a table temp(name int,count int). It stores:-

a|count
1|10
1|8
1|4
1|2
2|10
2|6
2|1

I want it's rows to be numbered, corresponding to a given name(also, note that count has to be in decreasing order), i.e, :-

a|count|row
1|10   |1
1|8    |2
1|4    |3
1|2    |4
2|10   |1
2|6    |2
2|1    |3

I tried How to show row numbers in PostgreSQL query? this post, but it just seems to number it from 1 to 7 and not name-wise. Can someone please help me with this? Thanks!

Answer 1

Use row_number() function

select a, count, row_number() over(partition by a order by count desc) as rn
from tablename