How can I get a random order of returned rows in mysql query

Question

I have MySQL query, that returns set of rows. What I need is to get them in random order, each time that the query is executed.

For example, I have query

SELECT id,id_banner,name FROM module_banner

And it returns me 3 rows with ids - 1,2,3

I want to get them in random order - 3,2,1 2,3,1 1,3,2 and so on.

Let me know if the question is not clear

P.S.

Is there solution without RANDOM() function ?

check [this](http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function) — Fahim Parkar, Mar 04 '12 at 08:02
This is demo query. The one I use is complex and RAND() will cause slow perfomance — Akhmed, Mar 04 '12 at 08:08
_why_ do you need random results? What is the rest of your situation like - there may be a better way to get the results you need. Please note that, in SQL, unless an `ORDER BY` statement is specified, your results _are_ 'random' - you will not be able to guarantee the order of the returned results. — Clockwork-Muse, Mar 04 '12 at 08:34
In order to generate a random query result order, you need -- at the very least -- one "random" number. How do you propose generating a pseudo random number without RAND()? Do you have another language available? — slypete, Mar 07 '12 at 02:51

score 4 · Answer 1 · answered Mar 04 '12 at 07:33

4

You want to use ORDER BY RAND():

SELECT id,id_banner,name FROM module_banner ORDER BY RAND()

answered Mar 04 '12 at 07:33

rjz

16,182
3
36
35

Fahim Parkar · Answer 2 · 2012-03-04T08:14:08.143

2

Just add ORDER BY RAND() at the end of your query.

Query in-general will be

SELECT field1, field2, ... , field(n) FROM TableName ORDER BY RAND()

In your case it would be

SELECT id, id_banner, name FROM module_banner ORDER BY RAND()

Update 1

While searching I found one article & You should read this article : "Do not use ORDER BY RAND() or How to get random rows from table?"

edited Mar 04 '12 at 08:14

answered Mar 04 '12 at 07:35

Fahim Parkar

30,974
45
160
276

It is not acceptable for me. In this article, he use generated numbers in LIMIT, but I have my own limit and I can't replace it with this numbers to generate random rows. But thanks for that info. – Akhmed Mar 04 '12 at 08:54
@Ahmed: can you tell the scenario that you have and what you want to achieve?? Maybe that way we can help you out... – Fahim Parkar Mar 04 '12 at 08:56
There is sql query executed on a few tables with limit of rows. Actually query returns banners. Count of returned rows can be limited ( for example 10), banners displayed on the page in different order, each time page loaded – Akhmed Mar 04 '12 at 09:41
edit question and add this info there so that all can read there... sorry for not informing earlier. Whatever info related to question should come in question... – Fahim Parkar Mar 04 '12 at 09:43
which language are you using for front-end?? In PHP you have `shuffle`. I java you can use random etc... – Fahim Parkar Mar 04 '12 at 09:44

score -1 · Answer 3 · answered Mar 04 '12 at 07:36

-1

SELECT id,id_banner,name
  FROM module_banner JOIN
       (SELECT CEIL(RAND() *
                    (SELECT MAX(id)
                       FROM module_banner)) AS id
        ) AS r2
       USING (id);

Reference

answered Mar 04 '12 at 07:36

Rizwan Mumtaz

3,875
2
30
31

How can I get a random order of returned rows in mysql query

3 Answers3

Update 1