MySQL dense_rank not skipping numbers

Question

I fairly new to MYSQL and I'm having trouble getting dense rank to work correctly, I was hoping someone would be able to help adjust my query to get the desired results. Issue I'm having is after a ranking that is the same it goes to the next number instead of skipping a number. I have shown both current results and desired results below.

        SELECT  gameid,
            score
         , IF(score <> @pscore,@i:=@i+1,@i:=@i) rank
         , @pscore := score          
        FROM playerstats x
        , (SELECT @i:=0,@prev:='',@pscore:='') vars 
        ORDER 
        BY score DESC;

Current Results:

Current Results

Desired Results:

Desired Results

please read https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-queryand add also the data that give that result — nbk, Jun 04 '20 at 15:49

nbk · Accepted Answer · 2020-06-05T09:57:20.750

Basically you need to sort the tdata before you rank it. MySQL

CREATE TABLE playerstats  (
  `Score` INTEGER,
  `Player` VARCHAR(11)
);

INSERT INTO playerstats 
  (`Score`, `Player`)
VALUES
  ('2543', 'jkoffa'),
  ('2204', 'probins'),
  ('2010', 'rwatson'),
  ('2010', 'nbk'),
  ('2010', 'tthamos'),
  ('1950', 'en,acdonald'),
  ('1927', 'dmaginis');

SELECT
`Player`
,IF(@score <> `Score` , @rn := @rn + @cor +1, @rn:= @rn) rnknumber
,IF(@score = `Score` , IF(@cor > 0,@cor := @cor +1,@cor := 1), @cor := 0) correction
,@score := `Score` 'Score'
FROM
(SELECT `Score`, `Player` FROM playerstats ORDER BY Score DESC) t1
, (SELECT @score := -1) t2
,(SELECT @rn := 0) t3,(SELECT @cor := 0) t4

Player      | rnknumber | correction | Score
:---------- | :-------- | ---------: | ----:
jkoffa      | 1         |          0 |  2543
probins     | 2         |          0 |  2204
rwatson     | 3         |          0 |  2010
nbk         | 3         |          1 |  2010
tthamos     | 3         |          2 |  2010
en,acdonald | 6         |          0 |  1950
dmaginis    | 7         |          0 |  1927

db<>fiddle here

for Mariadb 10.3

CREATE TABLE playerstats  (
  `Score` INTEGER,
  `Player` VARCHAR(11)
);

INSERT INTO playerstats 
  (`Score`, `Player`)
VALUES
 ('2010', 'tthamos'),
  ('1950', 'acdonald'),
  ('1927', 'dmaginis'),
  ('2010', 'nbk'),
  ('2543', 'jkoffa'),
  ('2204', 'probins'),
  ('2010', 'rwatson');

SELECT
`Player`
,IF(@score <> `Score` , @rn := @rn + @cor +1, @rn:= @rn ) rnknumber
,IF(@score = `Score` , IF(@cor > 0,@cor := @cor +1,@cor := 1), @cor := 0) correction
,@score := `Score` 'Score'
FROM
(SELECT `Score`, `Player` FROM playerstats ORDER BY Score DESC LIMIT 18446744073709551615) t1
, (SELECT @score := -1) t2
,(SELECT @rn := 0) t3,(SELECT @cor := 0) t4

Player   | rnknumber | correction | Score
:------- | :-------- | ---------: | ----:
jkoffa   | 1         |          0 |  2543
probins  | 2         |          0 |  2204
tthamos  | 3         |          0 |  2010
nbk      | 3         |          1 |  2010
rwatson  | 3         |          2 |  2010
acdonald | 6         |          0 |  1950
dmaginis | 7         |          0 |  1927

db<>fiddle here

nbk - Thank you for your quick response. It's still giving me the same results as I was having before. Ranking is going 1, 2, 3, 3, 4, 5 instead of 1, 2, 3, 3, 5, 6 — Syndct, Jun 04 '20 at 15:57
So i corrected your query. you had the right idea, it need only a bit other logic., and next time add the data, as text that you want to rank, so that i don't have to ocr yourpictures — nbk, Jun 04 '20 at 17:46
Sorry I posted another answer as it doesn't seem to work putting code in here. — Syndct, Jun 04 '20 at 21:11
That is awesome nbk, thanks again - last question, if we wanted to display the ranking numbers how one go about doing that in Mariadb? — Syndct, Jun 04 '20 at 22:01
Sorry i miss oen comment chracter. this should now show also the rank, if you want only the three rows ,you have to write a SELECT around that only uses the three columns and you can then also swithc the order of columns — nbk, Jun 04 '20 at 22:09
This works great but ran into a minor snag, if more than two people have a tied score it only adds one to the next number. — Syndct, Jun 05 '20 at 00:38

score 0 · Answer 2 · answered Jun 04 '20 at 21:10

nbk - I apologize for the pictures will make sure to use text next time. Thank you again for your time. Unfortunately it only works if data is entered in the correct order. If you were to take the bottom three entries and put them in first then tthomos would be ranked #1.

CREATE TABLE playerstats  (
  `Score` INTEGER,
  `Player` VARCHAR(11)
);

INSERT INTO playerstats 
  (`Score`, `Player`)
VALUES
  ('2010', 'tthamos'),
  ('1950', 'acdonald'),
  ('1927', 'dmaginis'),
  ('2543', 'jkoffa'),
  ('2204', 'probins'),
  ('2010', 'rwatson');

then ran:

SELECT
`Player`
,IF(@score <> `Score` , @rn := @rn + @cor +1, @rn:= @rn) rnknumber
,IF(@score = `Score` , @cor := 1, @cor := 0) correction
,@score := `Score` 'Score'
FROM
(SELECT `Score`, `Player` FROM playerstats ORDER BY Score DESC) t1
, (SELECT @score := -1) t2
,(SELECT @rn := 0) t3,(SELECT @cor := 0) t4

You would get:

tthamos   1     0   2010
acdonal   2     0   1950
dmaginis  3     0   1927
jkoffa    4     0   2543
probins   5     0   2204
rwatshon  6     0   2010

Your answer is wrong, you didn't get the result that you need, with my query it doesn't matter which order you use it sorted it by score see https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=02e2451be7be7d99ab4fa3815d1993f6 — nbk, Jun 04 '20 at 21:18
I'm using MySQL MariaDB 10.3, and if I choose that instead of mysql8 I get the same results as I described. https://dbfiddle.uk/?rdbms=mariadb_10.3&fiddle=02e2451be7be7d99ab4fa3815d1993f6 Do you think it's possible to get a result I'm looking for in 10.3 MariaDB or should I look at moving to mysql 8. Again nbk I appreciate your time. — Syndct, Jun 04 '20 at 21:41
see my changed answer. You should also have told us thus from the beginning, that is why a dbfiddle example from the Beginning, had got you faster an answer — nbk, Jun 04 '20 at 21:52

MySQL dense_rank not skipping numbers

2 Answers2