CODEWITHSUNDEEP
mysqlsqlselectsql-order-by
aslamdoctor
aslamdoctor

Reputation: 3935

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

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

Upvotes: 2

Views: 642

Answers (3)

Shailesh Mistry
Shailesh Mistry

Reputation: 1

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

Upvotes: 0

Javlon Ismatov
Javlon Ismatov

Reputation: 194

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

Upvotes: 0

O. Jones
O. Jones

Reputation: 108839

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.

Upvotes: 1

Related Questions