Show absolute counter in mysql

Question

I have this relation:

╔═══════════════════╗
║ i++ name  score   ║
╠═══════════════════╣
║ 1   Will  123     ║
║ 2   Joe   100     ║
║ 3   Bill  99      ║
║ 4   Max   89      ║
║ 5   Jan   43      ║
║ 6   Susi  42      ║
║ 7   Chris 11      ║
║ 8   Noa   9       ║
║ 9   Sisi  4       ║
╚═══════════════════╝

with this sql:

set @username = ?;
set @uuid = ?;
SELECT * FROM (
    -- Get those who scored worse (or tied)
    (    SELECT s.*
           FROM quiz.score s
     CROSS JOIN (SELECT points FROM quiz.score WHERE username = @username) and uuid=@uuid ref
          WHERE s.points <= ref.points AND username <> @username
       ORDER BY s.points DESC
          LIMIT 2)
    UNION
    -- Get our reference point record
    (SELECT s.* FROM quiz.score s WHERE username = @username and uuid=@uuid)
    UNION
    -- Get those who scored better
    (    SELECT s.*
           FROM quiz.score s
     CROSS JOIN (SELECT points FROM quiz.score WHERE username = @username and uuid=@uuid) ref
          WHERE s.points > ref.points AND username <> @username
       ORDER BY s.points ASC
          LIMIT 2)
) slice
ORDER BY points ASC;

I got this result

╔═══════════════════╗
║ id++ name score   ║
╠═══════════════════╣
║ 1   Bill  99      ║
║ 2   Max   89      ║
║ 3   Jan   43      ║
║ 4   Susi  42      ║
║ 5   Chris 11      ║
╚═══════════════════╝

but I need that result:

╔═══════════════════╗
║ id++ name score   ║
╠═══════════════════╣
║ 3   Bill  99      ║
║ 4   Max   89      ║
║ 5   Jan   43      ║
║ 6   Susi  42      ║
║ 7   Chris 11      ║
╚═══════════════════╝

I need an iterator column thats count the absolute position of the founded rows in the whole table. Do you know how I can handle this?

I am working on an online highscore. A highscore can be identified by username and uuid. with the sql in my question I am able to show a subview of thousands of entries. unfortunality I want to display the current score of the user additional. and thats the problem. how can I show a score that is based on all rows in a subview? — nano_nano, Oct 10 '13 at 19:32
@Mosty Mostacho lets pretend I have 100000 entries. I am not sure if I should create the subview with mysql or on serverside with java. I am looking for a solution without having high performance risk. — nano_nano, Oct 10 '13 at 19:39
Haven't I done that in my answer to your [previous question](http://stackoverflow.com/questions/19211700/sql-show-partition-based-on-calculated-column-with-mysql/19231601#19231601)? — PM 77-1, Oct 10 '13 at 20:06
yes but I still dont know how to implement the counter. maybe a java solution is better... — nano_nano, Oct 10 '13 at 20:08

score 1 · Accepted Answer · answered Oct 10 '13 at 20:48

This will run much faster than the query you have:

SELECT @rankOfSearch := count(*) FROM t
WHERE score >= (
  SELECT score FROM t
  WHERE id = 5
)
ORDER BY score DESC;

SELECT id, name, score FROM (
  SELECT t.*, @rank := @rank + 1 rank
  FROM t, (SELECT @rank := 0) init
  ORDER BY score DESC
) s
WHERE rank BETWEEN @rankOfSearch - 2 AND @rankOfSearch + 2
ORDER BY rank

Output:

| ID |  NAME | SCORE |
|----|-------|-------|
|  3 |  Bill |    99 |
|  4 |   Max |    89 |
|  5 |   Jan |    43 |
|  6 |  Susi |    42 |
|  7 | Chris |    11 |

Just make sure to replace id = 5 to whatever way of looking for the "middle" record you currently have (or might have in the future).

score 0 · Answer 2 · answered Oct 10 '13 at 20:57

0

If the scores are always in descending order in correlation with the ID, you can use this:

SET middle = 5
SET max = middle + 2;
SET min = middle - 2;

SELECT *
FROM quiz_score
WHERE id BETWEEN min AND max

answered Oct 10 '13 at 20:57

Lloyd Banks

35,740
58
156
248

Show absolute counter in mysql

2 Answers2