Show value in a special position with MySQL

Question

I have a simple table, but want to add a code so i can put a special result in a chosen position.

SELECT * FROM table ORDER BY rating DESC LIMIT 10;

I also have a column called position. If this has 4 as value i want it to show as the 4th result, if value is 7 as the 7th row and so on. Else I want it to order by rating.

Is this possible? With CASE maybe?

I tried the following

SELECT * FROM table 
    ORDER BY 
    CASE WHEN position = 4 THEN 4
    WHEN position = 9 THEN 9 
    ELSE 0 END,
    rating DESC
LIMIT 10;

This just hides those with values 4 and 9 in position.

SELECT * FROM table ORDER BY rating DESC LIMIT 10;

should give the following table

Name	Rating	Position
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

I rather want a code to show following table

Name	Rating	Position
Mike Brown	93	0
John Doe	85	0
Alex Johnson	78	0
Sarah Lee	52	4
Jane Smith	72	0
Emily White	60	0
Robert Miller	49	0
David Clark	38	0
Emma Davis	39	9
Olivia Hall	30	0

Answer 1

I can get close (it's not perfect) by first setting a row-number based on the rating

SELECT *
   , row_number() over (order by rating) rn
FROM table

Then I can use both that and the row number at the same level to determine the order:

SELECT *
FROM (
    SELECT *
       , row_number() over (order by rating) rn
    FROM table
) t
ORDER BY coalesce(nullif(position, 0), rn), position desc

The problem is it will lose a spot every time we do have a position match. Getting around this requires knowing how many rows have a set position value, so we can leave a place for them.

Answer 2

I have this split into three steps...

---

1. filter the data down to 10 rows (adjustable by changing the two 10's to n)

If you want 10 rows, you want all rows where the position override is 10 or less, with the remaining rows based on their rating (highest first)

WITH
  filtered AS
(
  SELECT
    *
  FROM
    example
  WHERE
    position <= 10
  ORDER BY
    position DESC,
    rating   DESC
  LIMIT
    10
),

---

2. rank the data in two batches (normal, and "fixed position")

The "normal" data (position = 0) just get ranked normally, based on rating, from 1 to n, in descending order.

• in the sample data's case from 1 to 8
• as there are 8 "normal" rows

The "fixed position" data (position <> 0) gets ranked on the position but in ascending order.

• Sarah Lee with position = 4 gets ranked 1
• Emma Davis with position = 9 gets ranked 2

  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
)

---

3. sort the data to the final positions

The final step is to sort based on the combination of the initial_rank from above, but adjusted for the existence of "fixed position" rows.

First, we put all the fixed position rows in the same position as the row we want them to follow.

For Sarah Lee it's simple

• she has position = 4
• we want to put after the third highest rating
• so, we'll put her interim position at 3 (Same as Alex)

For Emma Davis we need to adjust that slightly

• she has position = 9
• but we already have two people at position 3 (Alex Jackson & Sarah Lee)
• which means that we want to put Emma after the seventh highest rating
• that will put her after 7 "normal" rows and Sarah
• which would be 9th overall
• so, we'll put her interim position at 7 (Same as David)

"Conveniently" this means that the position we want to put each "fixed position" row after is simple...

• interim_position = position - initial_rank
• Sarah Lee = 4 - 1 = 3
• Emma Davis = 9 - 2 = 7

Then, to account for all "interim positions" that have more than one row, we also sort by the position column

• "normal" rows have 0, so they always go first
• "fixed position" rows have ascending values, so they go next

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

---

DEMO ON dbfiddle.uk : https://dbfiddle.uk/7g2sgApt

(Includes a shorter version, but it will be slower on large datasets due to LIMITing last rather than first.)

---