CODEWITHSUNDEEP
mysqlsql
EvilKarter
EvilKarter

Reputation: 377

MySQL Order by Rand() at result top

I have a list of entries in my table. Some of the entries are special and have a flag "is_premium" set to true. I want now get a List out of my table where all premium entries are at the top but also ordered by rand. The problem is that after the premium entries normal entries should be shown. But they shouldn't be sorted rand. They should be sorted by attributes like published_date and update_datetime.

My current solution is this query: 

SELECT
   first_table.*,
   sorting
FROM
   first_table
LEFT JOIN
   (
      SELECT
         id,
         RAND() AS sorting
      FROM
         first_table
      WHERE
         is_premium = 1
   ) p
      ON p.id = first_table.id
INNER JOIN
   (
      SELECT
         first_table.id,
      FROM
         first_table
      WHERE
         CURDATE() BETWEEN published_date AND DATE_ADD(published_date,INTERVAL valid_days DAY)
   ) a
      ON inserate.id=a.id 
ORDER BY
   is_premium DESC,
   sorting DESC,
   published_date DESC,
   update_date DESC

My problem now is that not only the premium entries have set the sorting value but also the normal entries. and I don't know how to solve this problem.

Upvotes: 0

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I think you just need an extra ORDER BY key:

ORDER BY is_premium DESC,
         (CASE WHEN is_premium THEN rand() END),
         sorting DESC,
         published_date DESC,
         update_date DESC

Upvotes: 1

Related Questions