Mysql: Query which orders by default and rank (pin/hold up entries?) I have a products table which contains all my products. Those products table gets filled permanently with new products. However, I want to have the possibility to &quot;hold up&quot;/&quot;pin&quot; certain products to a place in the returned query collection.
Means, I want to set something like rank_index which contains the number the product should have in the returned query collection.
Example:
id     title                rank_index
1      An awesome product
2      Another product      3
3      Baby car
4      Green carpet         1
5      Toy        

Lets assume the default order would be the id, descending. But descending? Because as I pointed out the products table gets permanently filled with new products and I always want to show the newest product on top. However, when e.g. an product has a rank_index of 2 the database always should return this entry as the second no matter how money new products are written to the database!
The result of the table above would be:
id     title                rank_index
4      Green carpet         1
5      Toy
2      Another product      3
3      Baby car
1      An awesome product

Is this somehow possible to do (without recursion)? The rank_index column was just an idea of mine. I mean.. I also could do this on the php side and do a normal query which does only include the products without an rank_index and one which only contains products with an index_rank and order them manually on the php side. Do you would recommend this? Would that be faster?
Furthermore, late on I want to add a perfomance_index which is not an +1 incrementing index but rather an index which show the performance of an product. This should be sorted descending as well if that makes any difference to this problem.
This here is doing a good job but only for ascending tables, I want a descending table with products being added permanently!
SELECT *
FROM table
ORDER BY IF(rank_index IS NULL, id, rank_index - 0.5)

Btw: I am using Laravel 8 if this makes any difference.
Kind regards
You can add to the ORDER BY a IF ot CASE, depending on the complexity or the new order.
In you case you ned to add the max(id) into the mix


CREATE TABLE table1 (
  `id` INTEGER,
  `title` VARCHAR(18),
  `rank_index` VARCHAR(4)
);

INSERT INTO table1
  (`id`, `title`, `rank_index`)
VALUES
  ('1', 'An awesome product', NULL),
  ('2', 'Another product', '3'),
  ('3', 'Baby car', NULL),
  ('4', 'Green carpet', '1'),
  ('5', 'Toy', NULL);




    SELECT t1.id, t1.`title`,t1.`rank_index`
    FROM table1  t1 CROSS JOIN ( SELECT MAX(id)  maxid FROM table1) t2 
    ORDER BY IF(rank_index IS NULL, maxid - id + 1 , rank_index - 0.5) ASC


id | title              | rank_index
-: | :----------------- | :---------
 4 | Green carpet       | 1         
 5 | Toy                | null      
 2 | Another product    | 3         
 3 | Baby car           | null      
 1 | An awesome product | null      


db&lt;&gt;fiddle here