SQL sort by, order by case when, item to specific position

Question

How to put item in a specific position?

I'd like to put item on a 5th position:

`id`='randomId243'

So far my sorting looks like that:

ORDER BY
    CASE
        WHEN `id` = 'randomId123' THEN 0
        WHEN `id` = 'randomId098' THEN 1
        ELSE 2
    END, `name` ASC

I don't know yet which id will be in position 2,3,4. I'd prefer to avoid run another query/subquery to get ids of items from position 2-4

So final order should be like this:

• randomId123
• randomId098
• just item alphabetical
• just item alphabetical
• just item alphabetical
• randomId243
• just item alphabetical

Answer 1

Use ROW_NUMBER() window function for your current ordering to rank the rows initially.
Then create 3 groups of rows: the rows on top, the row with 'item5' and the rows below 'item5' which will be assembled with UNION ALL and sorted by group and row number:

WITH cte AS (
    SELECT *, 
           ROW_NUMBER() OVER (ORDER BY CASE id WHEN 'item0' THEN 0 WHEN 'item1' THEN 1 ELSE 2 END, name) AS rn
    FROM tablename
)  
SELECT id, name
FROM (
  SELECT * FROM (SELECT *, 1 grp FROM cte WHERE id <> 'item5' ORDER BY rn LIMIT 5)
  UNION ALL
  SELECT *, 2 FROM cte WHERE id = 'item5'
  UNION ALL
  SELECT * FROM (SELECT *, 3 FROM cte WHERE id <> 'item5' ORDER BY rn LIMIT -1 OFFSET 5) -- negative limit means there is no limit
)
ORDER BY grp, rn;

Note that instead of:

ORDER BY CASE id WHEN 'item0' THEN 0 WHEN 'item1' THEN 1 ELSE 2 END, name

you could use:

ORDER BY id = 'item0' DESC, id = 'item1' DESC, name

See a simplified demo.