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

Question

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.

juergen d · Accepted Answer · 2014-11-20 11:26:09Z

1

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()

edited Nov 20, 2014 at 11:26

answered Nov 20, 2014 at 9:24

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

pathe.kiran Over a year ago

its shuffling all results... i need first diamond results randomly..and then platinum randomly...your query shuffling all main_plan

juergen d Over a year ago

Then add example data to your question and expected output.

embert Over a year ago

Please add explanation on what the code actually does, even if for some it is obvious on first sight.

pathe.kiran Over a year ago

can u check my question again...??

Shijin TR · Accepted Answer · 2014-11-20 12:20:57Z

0

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()

edited Nov 20, 2014 at 12:20

answered Nov 20, 2014 at 11:43

Shijin TR

7,80911 gold badges64 silver badges126 bronze badges

3 Comments

pathe.kiran Over a year ago

your answer won't work when i there is more than 2 values in my column...But still thanks for help..

Shijin TR Over a year ago

@user2530951 Please check my updated query,This may help you.I think

nalply Over a year ago

This has been automatically flagged as low-quality. Perhaps you should add some explanation about what your code does exactly, or your answer could be deleted by the community.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related