Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL select user's place

Tags:

mysql

rating

I have a table users:

id  |   rating
1       4
2       -1
3       12
4       0
5       2
6       0

How I can select user's place ordering by rating where id = N and that users with similar rating have the same place in the top?

UPD: I want to output:

If userid = 1, his rank is 2, if userid = 2, his rank is 5, if userid = 3, rank is 1, ... But if userid = 4 or 6 their rank are 4 because rates are similar.

like image 737
Isis Avatar asked Aug 31 '12 11:08

Isis


People also ask

Where are users stored in MySQL?

MySQL stores accounts in the user table of the mysql system database. An account is defined in terms of a user name and the client host or hosts from which the user can connect to the server.

How can I see all MySQL users and passwords?

So for example, to show MySQL users' username, password and host, we'll modify the sql query to accordingly as such: mysql> select user, password, host from mysql. user; The above sql query will present you with a list of users and their respective user name, password and database host.

How can I see MySQL users in Ubuntu?

Show All MySQL Users A user account in MySQL consists of two parts: a user name and hostname. Use the desc mysql. user; statement to display information about the table's columns. Once you know the column name, you can run a query against a selected data.


1 Answers

I think you want to find the rating for asll user_id's`:

SELECT id,
       IF(rating <> @var_rating_prev, (@var_rank:= @var_rank + 1), @var_rank) AS rank,
       (@var_rating_prev := rating) AS rating
FROM table_name a,(SELECT @var_rank := 0, @var_rating_prev := 0) r
ORDER BY rating DESC;

Example @ sqlfiddle

If you don't want to change the order of records in output then try this:

SELECT a.*, b.rank
FROM test_table a
     INNER JOIN (
            SELECT id,
                   IF(rating <> @var_rating_prev, (@var_rank:= @var_rank + 1), @var_rank) AS rank,
                   (@var_rating_prev := rating) AS rating
            FROM test_table a,(SELECT @var_rank := 0, @var_rating_prev := 0) r
            ORDER BY rating DESC
        ) b
         ON a.id = b.id
ORDER BY a.id;
like image 69
Omesh Avatar answered Oct 23 '22 08:10

Omesh