Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

execute a select statement with order of rows randomized every execute?

 
Winston Liek
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys,

I have an mysql query and it returns around 500 rows and is executed multiple times.
How can I randomize the position of rows returned by query each time I execute it?
I'm not sure if it's possible in mySQL statement but if not, how can I randomize a list?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure it will work, but you can try:
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here you'll find a great post handling several cases, from simple, to gaps, to non-uniform with gaps.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's interesting, although I think it deals with cases when you want to pick one (or a generally a few) rows randomly out of a large table. In that case, sorting the entire table to throw all but a few rows away is really needlessly expensive.

If the goal is to fetch all rows from a query, ordered randomly, then I'd say that the simple order by rand() should be quite effective, for a reasonable amount of rows (the 500 rows in the original post still seem "reasonable" to me). Fetching out millions of rows in random order this way certainly wouldn't be a good idea.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In Microsoft SQL Server you can do something similar (and we use it in our tests). Using TABLESAMPLE you can limit the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. Combined with the REPEATABLE option you can choose to return the same rows or return a different set of rows using another seed value.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic