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
– Pavithra Gunasekara, Commented Jun 16, 2011 at 7:30
Why don't you randomize the rows on the PHP side after you have retrieved them? — Tamás
– Tamás, Commented Jun 16, 2011 at 7:48
currently I have used that way to do it, but I thought this approach will be quite good. — Pavithra Gunasekara
– Pavithra Gunasekara, Commented Jun 16, 2011 at 8:00

Connor Smith · Accepted Answer · 2011-06-16 07:34:43Z

2

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

answered Jun 16, 2011 at 7:34

Connor Smith

1,3147 silver badges11 bronze badges

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

1 Comment

Pavithra Gunasekara Over a year ago

Links you shared, helped me a lot to get an idea of using different methods instead using random()... Thanks a lot...

Community · Accepted Answer · 2017-05-23 10:08:06Z

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, 2017 at 10:08

CommunityBot

11 silver badge

answered Jun 16, 2011 at 7:28

Tamás

48.2k12 gold badges107 silver badges125 bronze badges

2 Comments

Pavithra Gunasekara Over a year ago

I use postgresql... I used ORDER BY RAND() too but no luck :(

Pavithra Gunasekara Over a year ago

you are a savior thanks a lot, indeed DISTINCT screwed it up earlier... :-)))

Collectives™ on Stack Overflow

randomize drupal database query

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related