CODEWITHSUNDEEP
mysqlsql
Shafizadeh
Shafizadeh

Reputation: 10340

How to order results randomly using mysql?

I have a table like this:

// mytable
+----+-------+-------+
| id | color | numb  |
+----+-------+-------+
| 1  | red   | 1     |
| 2  | red   | 2     |
| 3  | red   | 3     |
| 4  | blue  | 1     |
| 5  | blue  | 2     |
| 6  | green | 1     |
| 7  | green | 2     |
| 8  | green | 3     |
| 9  | green | 4     |
+----+-------+-------+

Now I want a different order for each request randomly (dynamic order). For e.g here is the query:

select * from mytable where color = 'green' order by {?};

First execution:

// newtable
+----+-------+-------+
| 7  | green | 2     |
| 6  | green | 1     |
| 8  | green | 3     |
| 9  | green | 4     |
+----+-------+-------+

Second execution:

// newtable
+----+-------+-------+
| 9  | green | 4     |
| 8  | green | 3     |
| 6  | green | 1     |
| 7  | green | 2     |
+----+-------+-------+

And so on ..., There isn't any specific rule .. just randomly. Well, Is there any mysql function or any approach for doing that?

Upvotes: 0

Views: 28

Answers (2)

Anton
Anton

Reputation: 420

You can try the following:

     SELECT * FROM mytable
     WHERE color = 'green'
     ORDER BY NEWID()

EDIT:

     ORDER BY RAND()

also works

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use order by rand(). The rand() function produces a random value and is documented here.

Upvotes: 3

Related Questions