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](https://stackoverflow.com/questions/36840662/find-a-users-position-on-a-ordered-sql-table) 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

```