Sorting Table and get Position of row

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

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>

StackExchange Database Administrators Q#87003, answer score: 3