sqlsqlitecasesql-order-byunion-all

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


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

Solution

  • 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.