1

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 "hold up"/"pin" 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

0
1

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<>fiddle here

0
0

I think you just have to add a descending clause to your order by, something like this:

SELECT *
FROM table
ORDER BY IF(rank_index IS NULL, id, rank_index - 0.5) DESC

The -0.5 I'm assuming is your own logic but certainly isn't a necessity to order by descending.

Also sorting is probably faster in the database then on the PHP side but either one probably has negligible differences.

2
  • 1
    this kinda works... rank_index is representing the row number of the row. So, weather ranking desc or asc I always want to have the row with the row number 2 on the second place from top.... with your answer I have now the table ordered desc but the rank_index is also desc. Means, the row with rank_index is the second last row and not the second first row. Any idea on how to solve this?
    – Jan
    Nov 10 '20 at 7:42
  • 1
    I didn't realize you wanted ascending based on one field and descending based on the other. Looks like Nbk already provided a working solution in that case. Good luck!
    – J.D.
    Nov 10 '20 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.