skip to Main Content

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

2

Answers


  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.

    Login or Signup to reply.
  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
    • John Doe 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
    )
    

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

    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 John Doe we need to adjust that slightly

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

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

    • position - calculated_rank
    • Sarah Lee = 4 - 1 = 3
    • John Doe = 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/G4Vp0O6_

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search