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
I can get close (it’s not perfect) by first setting a row-number based on the rating
Then I can use both that and the row number at the same level to determine the order:
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 setposition
value, so we can leave a place for them.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)
2. rank the data in two batches (normal, and "fixed position")
The "normal" data (
position = 0
) just get ranked normally, based onrating
, from1
ton
, in descending order.1
to8
The "fixed position" data (
position <> 0
) gets ranked on theposition
but in ascending order.Sarah Lee
withposition = 4
gets ranked1
John Doe
withposition = 9
gets ranked2
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 simpleposition = 4
3
(Same as Alex)For
John Doe
we need to adjust that slightlyposition = 9
3
(Alex Jackson
&Sarah Lee
)John
after the seventh highest rating7
(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
column0
, so they always go firstDEMO ON dbfiddle.uk : https://dbfiddle.uk/G4Vp0O6_