Find position of user in a sorted table

Question

I have a table with user input. I want to count the number of inputs per user, sort based on the count and then find the row number.

In other words I want to find the rank of each user based on the number of inputs.

The database is in PostgreSQL 8.4. There does not seem to be any performance issues. The table has less than 1 000 000 rows.

This is a duplicate this question. Sadly that question has -3 as score and no answers.

The table is named table1, The user_name column has unique users.

user_name   | input
name1         txt input 1
name2         txt input 2
name1         txt input 3
name3         txt input 4
name1         txt input 5
name2         txt input 6

The query I want is this with one additional feature:

WITH temp_table AS ( 
    SELECT user_name, COUNT(*) 
    FROM table1 
    GROUP BY user_name 
    ORDER by count DESC ) 
SELECT name, row_number() OVER ( ) FROM temp_table;

OUtput:

user_name   | row_number
name1         1
name2         2
name3         3

How can I select a row from that table using the user_name? I tried this:

WITH temp_table AS ( 
    SELECT user_name, COUNT(*) 
    FROM table1 
    GROUP BY user_name 
    ORDER by count DESC ) 
SELECT name, row_number() OVER ( ) FROM temp_table
WHERE user_name = 'name2';

The output is always row_number 1

user_name   | row_number
name2         1

I expected

user_name   | row_number
name2         2

Answer 1

you can try below - just use a subquery

WITH temp_table AS ( 
    SELECT user_name, COUNT(*) 
    FROM table1 
    GROUP BY user_name 
    ORDER by count DESC 
) 
select * from 
(
SELECT name, row_number() OVER (order by null) FROM temp_table
)A where name='name2'

Answer 2

You can combine row_number() with group by directly in your base query.

select *
from (
   select user_name, 
          count(*) as cnt,
          row_number() over (order by count(*) desc) as rn
   from table1
   group by user_name
) t
where user_name = 'name2';  

This works because the group by is evaluated before the window functions. You can also write this as a CTE:

with temp_table as (
   select user_name, 
          count(*) as cnt,
          row_number() over (order by count(*) desc) as rn
   from table1
   group by user_name
)
select *
from temp_table
where user_name = 'name2';

---

Your row_number() doesn't work because your where clause limits the result to one row and window functions are applied after the where clause.