WatsMyName WatsMyName - 4 months ago 9
SQL Question

Myql Sort featured products randomly but normal product by date

Suppose my query is -

SELECT * FROM products ORDER BY is_featured DESC, created_date DESC


Where
is_featured
is a flag field in table which holds either 1 or 0,
Its obvious the above query returns set of records with all featured products at first (the lastest among which will come first) and then the normal products(the latest among which will come first).

My question: How can we rewrite the above query such that, Featured products comes first (But will be random) and then follows the normal products (sorted by created date).

I can sense the possible answer be write two separate queries, and join the resultset and iterate through the loop to display products. But wondering can it be achieved via single query?

Answer

One way I could think about is to add another calculated expression to the order by clause that returns a random value for featured products and a constant for other products, so it doesn't affect their order:

SELECT   *
FROM     products 
ORDER BY is_featured DESC, 
         CASE is_featured
              WHEN 1 THEN RAND()
              ELSE 1 -- Or some other constant
         END,
         created_date DESC