MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.6.9-MariaDB-1:10.6.9+maria~deb11


CREATE TABLE example (
  name            VARCHAR(32),
  rating          INT,
  position        INT
)
INSERT INTO
  example
VALUES
  ('Mike Brown   ', 93, 0),
  ('John Doe     ', 85, 0),
  ('Alex Johnson ', 78, 0),
  ('Jane Smith   ', 72, 0),
  ('Emily White  ', 60, 0),
  ('Sarah Lee    ', 52, 4),
  ('Robert Miller', 49, 0),
  ('Emma Davis   ', 39, 9),
  ('David Clark  ', 38, 0),
  ('Olivia Hall  ', 30, 0),
  ('Eleven       ',  1, 0)
;

Records: 11  Duplicates: 0  Warnings: 0


WITH
  filtered AS
(
  -- Get the "n" rows we need (in this case 10)
  -- > if position is less than or equal to "n", then it must be included
  -- > the remainder are included based on their rating
  SELECT
    *
  FROM
    example
  WHERE
    position <= 10
  ORDER BY
    position DESC,
    rating   DESC
  LIMIT
    10
),
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY IF(position = 0, 0,       1       )
          ORDER BY IF(position = 0, rating, -position) DESC
    )
      AS initial_rank
  FROM
    filtered
)
SELECT
  name, rating,
  IF(position = 0, initial_rank, position - initial_rank)  AS position_interim,
  position                                                 AS position_tiebreak
FROM
  sorted
ORDER BY
  IF(position = 0, initial_rank, position - initial_rank),
  position


name
rating
position_interim
position_tiebreak


Mike Brown
93
1
0

John Doe
85
2
0

Alex Johnson
78
3
0

Sarah Lee
52
3
4

Jane Smith
72
4
0

Emily White
60
5
0

Robert Miller
49
6
0

David Clark
38
7
0

Emma Davis
39
7
9

Olivia Hall
30
8
0


  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY rating DESC)
      AS interim_position
  FROM
    example
  WHERE
    position = 0

  UNION ALL

  SELECT
    *,
    position
    -
    ROW_NUMBER() OVER (ORDER BY position)
      AS interim_position
  FROM
    example
  WHERE
    position > 0

ORDER BY
  interim_position,
  position
LIMIT
  10


name
rating
position
interim_position


Mike Brown
93
0
1

John Doe
85
0
2

Alex Johnson
78
0
3

Sarah Lee
52
4
3

Jane Smith
72
0
4

Emily White
60
0
5

Robert Miller
49
0
6

David Clark
38
0
7

Emma Davis
39
9
7

Olivia Hall
30
0
8

name	rating	position_interim	position_tiebreak
Mike Brown	93	1	0
John Doe	85	2	0
Alex Johnson	78	3	0
Sarah Lee	52	3	4
Jane Smith	72	4	0
Emily White	60	5	0
Robert Miller	49	6	0
David Clark	38	7	0
Emma Davis	39	7	9
Olivia Hall	30	8	0