SQL select 3 bests scores and other data for each user order by scores

Question

May be you can help me. I would like to display an Hall of Fame From my table with rank. But i would like to select about Difficulty choice, 3 bests Score for each Pseudo in limit to 25 rows maximum order by Score desc. And for each, display all information in row.

I use MariaDB 10.3.31. My Table named 'Loopz'

Id	Pseudo	Score	Difficulty	Duration	PiecesPerMinute	LoopsPerMinute
1	Thy	1333	1	42932	44.00	1.33
2	P0lux	1433	1	43222	46.33	1.50
3	Sky	600	1	25348	20.28	0.88
4	Thy	2500	1	53124	46.94	2.05
5	Alex	1200	0	21000	30.35	1.23
6	Thy	4200	1	60652	48.17	2.24
7	P0lux	3364	1	52734	39.37	1.96

etc...

i Try this

SELECT r.*
FROM
(
    SELECT
        r.*,
        ROW_NUMBER() OVER(PARTITION BY r.Pseudo
                          ORDER BY r.Score DESC) rn
    FROM Loopz r
) r
WHERE r.rn <= 3 and Difficulty=1
ORDER BY r.Score DESC;

But i don't understand why it's missing some data. Some Pseudo are note selected, and some time display only 2 best scores...

Best regards

Answer 1

I think that this is what you are looking for. I've used GROUP_CONCAT to put the 3 scores on one row. I haven't applied the filter Difficulty = 1 because you have already said that you don't see all the rows that you want.
The first sort is ORDER BY Difficulty DESC and we have LIMIT 25. This means that as soon you have 25 persons with Difficulty = 1 there will not be anyone shown with Difficulty 0.

CREATE TABLE Loopz (
  Id int,
  Pseudo VARCHAR(25), 
  Score   int,
  Difficulty int, 
  Duration int,
  PiecesPerMinute decimal(5,2),   
  LoopsPerMinute decimal(5,2)
  );

insert into Loopz values
(1,'Thy',1333,1,42932,44.00,1.33),
(2,'P0lux',1433,1,43222,46.33,1.50),
(3,'Sky',600,1,25348,20.28,0.88),
(4,'Thy',2500,1,53124,46.94,2.05),
(5,'Alex',1200,0,21000,30.35,1.23),
(6,'Thy',4200,1,60652,48.17,2.24),
(7,'P0lux',3364,1,52734,39.37,1.96);

WITH ranking as
(SELECT
  Pseudo,
  Score,
  Difficulty,
  Rank() OVER (PARTITION BY Pseudo ORDER BY SCORE DESC) rn
FROM Loopz)
SELECT
  Pseudo,
  GROUP_CONCAT(Score ORDER BY Score) Scores,
  MAX(Difficulty) Difficulty
FROM ranking
WHERE rn < 4
GROUP BY Pseudo
ORDER BY 
  Difficulty DESC,
  MAX(Score) DESC
LIMIT 25;

Pseudo | Scores         | Difficulty
:----- | :------------- | ---------:
Thy    | 1333,2500,4200 |          1
P0lux  | 1433,3364      |          1
Sky    | 600            |          1
Alex   | 1200           |          0

db<>fiddle here