• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

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

 
Ranch Hand
Posts: 197
  • 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?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure it will work, but you can try:
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • 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.
 
It's a beautiful day in this neighborhood - Fred Rogers. Tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
    Bookmark Topic Watch Topic
  • New Topic