Sort records in random order for specific group of data using MySQL select query

Question

I have a below db table structure :

Tablename : agency_mst
Fields:
id
name
is_premium
date_added

I want to sort the data so that the agencies with is_premium=1 comes first and the rest of the data is sorted by date_added desc order.

But also the is_premium=1 records should be sorted in random order. so first set of premium agencies will have random order. How can I do that using MySQL Select query.

I have built this query partially but not sure how to filter specific set of data. Below is that query:

SELECT * FROM agency_mst
ORDER BY is_premium DESC, date_added DESC

Answer 1

How about

SELECT * 
  FROM agency_mst
 ORDER BY IF(is_premium=1, RAND(), -1.0), date_added DESC

That will use random ordering for the matching rows, then put the others last, and order them by date.

Be careful with performance, though. ORDER BY RAND() in any variant is a notorious performance antipattern in tables with many rows.

Answer 2

select t1.id,t1.name,t1.is_premium,t1.date_added from 
(select (ROW_NUMBER() over (order by id))* cast(CRYPT_GEN_RANDOM(10) as int) RND,* from agency_mst) t1
order by t1.RND

Answer 3

SELECT id, name, is_premium , date_added ,if(is_premium ,rand()*-15,0) as first_order
 FROM agency_mst
ORDER BY first_order, date_added DESC

Check This Using rand()*-15 you get -velue it show first and remain will be 0 and that will be order by date_added