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

If you just need to sort by the number after the id you can strip out the last character with a substring function and then just order by the substring that just has the number in it. — Brad, Feb 10 '23 at 16:37
Id could be different. I just put like that to show an order. — kkkkk, Feb 10 '23 at 16:39
This needs sample data, and some sort of logical sort other than "I want to put it here cause I want to." If the latter is your goal and your sort order is truly not derministic, you need another column to track and manage desired row number/placement by itself. — ClearlyClueless, Feb 10 '23 at 17:23
Your numbering is 0 based, right? Don't you mean *the 6th position*, after positions 0,1,2,3,4? — forpas, Feb 10 '23 at 17:28
I've edited ids to not mislead. I just have a requirement position 0 should be this id, 1 another id, 5th another id. Rest of unknown position just fill alphabetically. Ids can vary, but I know only this few on start. — kkkkk, Feb 10 '23 at 17:39
forpas could be like this. The problem is that I need to put an item on a specific position. It would be easier to do it in a memory, but sql is faster. The list is big. — kkkkk, Feb 10 '23 at 17:46

score 1 · Accepted Answer · answered Feb 10 '23 at 18:22

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.

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

1 Answers1