randomize drupal database query

Question

I'm executing a query which I want to randomize query results from a drupal database.

here's what I have done...

$res=db_query("SELECT DISTINCT nid FROM content_type_event ORDER BY RANDOM()");

but this query doesn't work. what's wrong with this query? please help me to solve this?

Thanks a lot...

it shows nothing, but when I remove ORDER BY RANDOM() from the query it works, is there any way I can randomize query results with db_query() function? — Pavithra Gunasekara, Jun 16 '11 at 07:30
Why don't you randomize the rows on the PHP side after you have retrieved them? — Tamás, Jun 16 '11 at 07:48
currently I have used that way to do it, but I thought this approach will be quite good. — Pavithra Gunasekara, Jun 16 '11 at 08:00

score 2 · Answer 1 · answered Jun 16 '11 at 07:34

You probably need to be using ORDER BY RAND() instead.

Also there is a bit you may want to know about ORDER BY RAND()'s performance if you are getting a considerable amount of results that will be randomized:

http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/

EDIT: Just saw your comment that you are using PostgreSQL, this looks like it could be helpful: http://www.petefreitag.com/item/466.cfm

Sorry I can't be a lot of much help, I don't use PostgreSQL myself

Links you shared, helped me a lot to get an idea of using different methods instead using random()... Thanks a lot... — Pavithra Gunasekara, Jun 16 '11 at 08:03

score 1 · Accepted Answer · edited May 23 '17 at 10:08

1

You did not tell us which database engine you are using, but maybe ORDER BY RAND() would be better.

Update: okay, PostgreSQL uses RANDOM() and not RAND() so that's okay. I've found this question which seems to suggest that ORDER BY RANDOM() should work and the error is likely to be elsewhere. Maybe it's DISTINCT that is screwing things up; try your query again without DISTINCT.

edited May 23 '17 at 10:08

Community

1
1

answered Jun 16 '11 at 07:28

Tamás

47,239
12
105
124

I use postgresql... I used ORDER BY RAND() too but no luck :( – Pavithra Gunasekara Jun 16 '11 at 07:31
you are a savior thanks a lot, indeed DISTINCT screwed it up earlier... :-))) – Pavithra Gunasekara Jun 16 '11 at 08:11

randomize drupal database query

2 Answers2