CODEWITHSUNDEEP
mysqlrandom
pathe.kiran
pathe.kiran

Reputation: 2484

How to use random function when there is field function in mysql

Here is my SQL query:

SELECT `main_bid`,`main_plan` FROM `business` 
order by FIELD(`main_plan`,'Diamond','Platinum','Gold','Listed')

I want to display first Diamond values randomly then platinum values randomly and so on.

I know there is a rand() MySQL function to get random results from tables. But i don't know how to use it when there is already one MySQL FIELD() function.

Sample data:

main_bid main_plan
------------------
   1      Diamond
   2      Diamond
   3      Diamond
   1      Platinum
   2      Platinum
   3      Platinum
   1      Gold
   2      Gold
   3      Gold

I need output as:

main_bid main_plan
------------------
   3      Diamond
   1      Diamond
   2      Diamond
   2      Platinum
   1      Platinum
   3      Platinum
   2      Gold
   1      Gold
   3      Gold

or

main_bid main_plan
------------------
   3      Diamond
   2      Diamond
   1      Diamond
   1      Platinum
   3      Platinum
   2      Platinum
   3      Gold
   2      Gold
   1      Gold


    SELECT `main_bid`,`main_plan` FROM `business` 
    order by FIELD(`main_plan`,'Diamond','Platinum'),rand()

This query is shuffling all results. i have to shuffle Diamond results first and then Platinum.

Upvotes: 0

Views: 90

Answers (2)

Shijin TR
Shijin TR

Reputation: 7768

Try this query instead of using FIELDS()

SELECT `main_bid`,`main_plan`,case  
     when main_plan = "Diamond" then "1" 
     when main_plan = "Platinum" then "2" 
     when main_plan = "Gold" then "3"
     end as plan_type  FROM `business` 
     order by plan_type,RAND()

Upvotes: 0

juergen d
juergen d

Reputation: 204776

SELECT main_bid, main_plan 
FROM business 
where main_plan in ('Diamond','Platinum')
order by case when main_plan = 'Diamond' then 1
              when main_plan = 'Platinum' then 2
         end,
         rand()

Upvotes: 1

Related Questions