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&gt; DROP DATABASE IF EXISTS mydb;
Query OK, 0 rows affected, 1 warning (0.03 sec)

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

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

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

mysql&gt; 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&gt;


QUERY #1 EXECUTED

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

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

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

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

mysql&gt;


QUERY #2 EXECUTED

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

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

mysql&gt;


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&gt; SET @given_user = 2;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql&gt;