MySQL – Sorting Table and Getting Position of Row

javajoin;MySQLtable

I have a Table with 3 rows. Player, Time, Server.

Now, I want 2 things and I don't know how.

For Server there are 3 worths available. 1, 2 and 3. Server and Player are a Primary Key together. User is a VARCHAR, Server and Time are Strings. I use 1,2 and 3 to make i easier to understand.

1st I want to sort the table by time and get the position of a specific player, but only from 1 Server.

Example:

User Time Server
2    10   1
1    50   1
2    60   3
1    20   2
1    100  3
2    70   2

Now, want to get which position the user 2 has

If I sort by time and server = 1
- user 2 would be number 2
- user 1 number 1
If server = 2
- user 2 would be 1
- user 1 would be 2

Then I want to get the User position, if I sort by the sum of the time of a user.
In this case, user 1 would be 1 (Sum of time: 170) and user 2 would be 2 (Sum of time:100).

I hope you can help me!
BTW, I want to use this in Java 😉

~java4ever/Robin

Best Answer

QUERY #1

position the user 2 has if I sort by time and server = 1

SET @given_user = 2;
SET @given_server = 1;
SET @pos = 0;
SELECT * FROM
(
    SELECT (@pos:=@pos+1) pos,User
    FROM playerstats
    WHERE server = @given_server
    ORDER BY time DESC
) A
WHERE User = @given_user;

QUERY #2

if I sort by the sum of the time of a user

SET @pos = 0;
SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
(
    SELECT User,SUM(time) TotalUserTime
    FROM playerstats
    GROUP BY User
) A ORDER BY TotalUserTime DESC;

YOUR SAMPLE DATA

mysql> DROP DATABASE IF EXISTS mydb;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.01 sec)

mysql> USE mydb
Database changed
mysql> CREATE TABLE playerstats
    -> (
    ->     User INT,
    ->     Time INT,
    ->     Server INT
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO playerstats VALUES
    -> (2, 10,1),
    -> (1, 50,1),
    -> (2, 60,3),
    -> (1, 20,2),
    -> (1,100,3),
    -> (2, 70,2);
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM playerstats;
+------+------+--------+
| User | Time | Server |
+------+------+--------+
|    2 |   10 |      1 |
|    1 |   50 |      1 |
|    2 |   60 |      3 |
|    1 |   20 |      2 |
|    1 |  100 |      3 |
|    2 |   70 |      2 |
+------+------+--------+
6 rows in set (0.01 sec)

mysql>

QUERY #1 EXECUTED

mysql> SET @given_user = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @given_server = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM
    -> (
    ->     SELECT (@pos:=@pos+1) pos,User
    ->     FROM playerstats
    ->     WHERE server = @given_server
    ->     ORDER BY time DESC
    -> ) A
    -> WHERE User = @given_user;
+------+------+
| pos  | User |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.09 sec)

mysql>

QUERY #2 EXECUTED

mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
    -> (
    ->     SELECT User,SUM(time) TotalUserTime
    ->     FROM playerstats
    ->     GROUP BY User
    -> ) A ORDER BY TotalUserTime DESC;
+------+------+---------------+
| pos  | User | TotalUserTime |
+------+------+---------------+
|    1 |    1 |           170 |
|    2 |    2 |           140 |
+------+------+---------------+
2 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

UPDATE 2014-12-26 15:32 EST

QUERY #3

SET @given_user = 2;
SET @pos = 0;
SELECT * FROM
(
    SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
    (
        SELECT User,SUM(time) TotalUserTime
        FROM playerstats
        GROUP BY User
    ) A ORDER BY TotalUserTime DESC
) AA
WHERE User = @given_user;

QUERY #3 EXECUTED

mysql> SET @given_user = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @pos = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM
    -> (
    ->     SELECT (@pos:=@pos+1) pos,User,TotalUserTime FROM
    ->     (
    ->         SELECT User,SUM(time) TotalUserTime
    ->         FROM playerstats
    ->         GROUP BY User
    ->     ) A ORDER BY TotalUserTime DESC
    -> ) AA
    -> WHERE User = @given_user;
+------+------+---------------+
| pos  | User | TotalUserTime |
+------+------+---------------+
|    2 |    2 |           140 |
+------+------+---------------+
1 row in set (0.06 sec)

mysql>

Related Solutions

MySQL Rank – How to Select, Rank, and Apply Point Score Based on Rank

Sample data:

CREATE TABLE v
    (`vote` varchar(8), `game` int, `year` int)
;

INSERT INTO v
    (`vote`, `game`, `year`)
VALUES
    ('player 1', 2, 2015),
    ('player 1', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 2', 2, 2015),
    ('player 3', 2, 2015),
    ('player 1', 2, 2015),
    ('player 3', 2, 2015),
    ('player 4', 2, 2015)
;

Query:

SELECT 
vote,
no_votes,
rank,
3 - rank + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points
FROM (

    SELECT 
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT 
        vote, 
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC

) q

Result:

|     VOTE | NO_VOTES | RANK | POINTS |
|----------|----------|------|--------|
| player 1 |        3 |    1 |    2.5 |
| player 2 |        3 |    1 |    2.5 |
| player 3 |        2 |    2 |      2 |
| player 4 |        1 |    3 |      1 |

see it working live in an sqlfiddle

Explanation:

This

        SELECT 
        vote, 
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

is simple enough, I hope :)

This

, (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery

is just a subquery to initialize some variables.

Here

    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes

in the first row @prevNoVotes holds the value of the previous row (or the value from the initialization subquery). @rc1, @rc2 and @rc3 are just rank counters. They count for each rank, how many people are on the same rank.
In the last row, the value of the current row is assigned to the variable. When the next row is processed, the variable will hold the value of the previous row in the first line of this part of the code.

And finally in the most outer part of the query we calculate the points distribution.

Feel free to ask any questions :)

UPDATE:

Added this table:

CREATE TABLE points
    (`r` int, `p` int)
;

INSERT INTO points
    (`r`, `p`)
VALUES
    (1, 3),
    (2, 2),
    (3, 1)
;

Added more logic :)

SELECT 
vote,
no_votes,
rank,
@rc := @rc + 1 AS rowcounter,
((SELECT p FROM points WHERE r = IF(rank = @prevRank, rank, @rc)) - 1) + (1 / CASE rank WHEN 1 THEN @rc1 WHEN 2 THEN @rc2 WHEN 3 THEN @rc3 END) AS points,
@prevRank := rank
FROM (
    SELECT 
    sq.*
    , @rank := if(@prevNoVotes = no_votes, @rank, @rank + 1) AS rank
    , @rc1 := if(@rank = 1, @rc1 + 1, @rc1)
    , @rc2 := if(@rank = 2, @rc2 + 1, @rc2)
    , @rc3 := if(@rank = 3, @rc3 + 1, @rc3)
    , @prevNoVotes := no_votes
    FROM
    (

        SELECT 
        vote, 
        COUNT(*) AS no_votes
        FROM v
        GROUP BY vote

    ) sq
    , (SELECT @rank := 0, @prevNoVotes := null, @rc1 := 0, @rc2 := 0, @rc3 := 0) var_init_subquery
    ORDER BY no_votes DESC
)q
, (SELECT @rc := 0, @prevRank := 1) row_counter_init
ORDER BY rank

see it working live in this sqlfiddle

MySQL – Get User’s Rank from Equation and Store in Table

OK - this should be easy for the first part, if you're willing to be a bit adventurous :-).

MariaDB has had computed columns (otherwise known as virtual or generated columns) for quite a while now. Otherwise, the latest milestone release of MySQL (5.7.7) also has them.

If you want to do this with a current GA version, you'll have to use TRIGGERs.

However, if you want to update the RANK (see a lovely post here) on each update of games_won/games_lost, then you'll have to call a PROCEDURE which will update RANK. MySQL's Stored Procedure programming language isn't sophisticated enough for this on its own, but see here for some ideas. It says that there can be rollback issues, but since you're updating only one table, it should be easier than most - good luck :-). BTW, I'd use float/double for games_won/_lost - otherwise, you might have problems with integer division.