3

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

  • The sum of times for User1 is 170 (50 + 20 + 100). That's right. The sum of times for User2 is 140 (10 + 60 + 70) not 100. – RolandoMySQLDBA Dec 25 '14 at 17:06
3

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>
  • Hi,Thanks for your help. The First Query works fine, but I want to get the position of a specific player sorted by whole Online Time. Will I have to iterate over the whole results to find the right user or is there a solution with mysql I get only 1 result? I tried a few querys, but none of them worked... – java4ever Dec 26 '14 at 19:55
  • That is what the QUERY #2. If that is not the right output, then what is the expect output ? Please put an example in the question. Regarding your question Will I have to iterate over the whole results to find the right user or is there a solution with mysql I get only 1 result?, yes you will have to iterate over the output of QUERY #2. Let me update that. – RolandoMySQLDBA Dec 26 '14 at 20:21
  • I have QUERY #3 ready for you – RolandoMySQLDBA Dec 26 '14 at 20:33
  • Ahh, Thank you! I didn't meant iterating over it in mysql, I meant whether I have to do a thing like: (rs is the ResultSet returned by the query) while (rs.next) { if (rs.getString("player").equals(playerwelookfor) { player found }}. Thank you very much! I had the same solution, but I had instead of "A" and "AA", "A" and "A". That was why it didn't work! //Edit: Would give you an big upvote if I could (not enough reputation) – java4ever Dec 26 '14 at 20:40
  • Ah, thank you. As you've probably realised, I'm new to Stackexchange ;-) – java4ever Dec 27 '14 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.