0

I don't think this is a duplicate of other rank statements as others do not seem to search the resulting table for a specific user

I have a table similar to:

|       | column1 | column2 | column3 | column4 |
|-------|---------|---------|---------|---------|
| user1 | 100     | 49      | 3       | 1,980   |
| user2 | 7       | 5       | 51      | 500     |
| user3 | 1       | 65      | 44      | 307     |
| user4 | 6       | 66      | 445     | 397     |
| user5 | 4       | 67      | 442     | 437     |

I currently have this statement to sort the table:

$statement = "SELECT *
            FROM tracker_players
            ORDER BY (column1 + column2 + column3 + column4)
            DESC";

The resulting table would be:

|       | column1 | column2 | column3 | column4 |
|-------|---------|---------|---------|---------|
| user3 | 1       | 65      | 44      | 307     | // 417
| user2 | 7       | 5       | 51      | 500     | // 563
| user4 | 6       | 66      | 445     | 397     | // 914
| user5 | 4       | 67      | 442     | 437     | // 950
| user1 | 100     | 49      | 3       | 1,980   | // 2132

Once I've sorted the table as such, I want to be able to find out what position of the table userX is in. i.e. user5 is in position 4 (or 3 if zero indexed).

Any help is appreciated. I can't seem to find any answers regarding this.

Thanks!

follmer
  • 1,050
  • 3
  • 14
  • 31
  • @Barmar is there a way I can answer my own question if it gets closed? I think it would be very helpful to see the answer I've got – follmer Apr 28 '18 at 17:37
  • The general philosophy of duplicates is that there's not much point in posting new answers, the answers at the linked question are adequate. Do you really think your answer demonstrates something new? – Barmar Apr 28 '18 at 20:33

1 Answers1

0
SELECT t.*, @rank := @rank + 1 as rank
FROM tracker_players t
CROSS JOIN (select @rank := 0) r
ORDER BY (t.column1 + t.column2 + t.column3 + t.column4) DESC
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • This assigns a rank to each row correctly, thank you. Do you know how I would go about finding what rank a specific `user` has from this query? Adding a `where` clause doesn't seem to do the trick – follmer Apr 28 '18 at 06:22
  • 2
    Add some explanation to your answer . Code only answers are not really useful to understand the concept other than just Copy-paste. – ADM Apr 28 '18 at 06:49
  • @Sej: For a specific user you would put a query around that: `select rank from (...) tmp where username = 'tom'` – juergen d Apr 28 '18 at 11:11