EvilKarter EvilKarter - 7 months ago 9
SQL Question

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.

-----EDIT-----
Thanks to Gordon Linoff I found a solution.

SELECT
first_table.*
FROM
first_table
WHERE
CURDATE() BETWEEN published_date AND DATE_ADD(published_date,INTERVAL valid_days DAY)
ORDER BY
is_premium DESC,
(CASE WHEN is_premium THEN rand()END)
published_date DESC,
update_date DESC

Answer

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