0

In my SQL i have a table with a column id & Order.On website data is displayed according to id & Order.But we want to change the way data is displayed we want to randomize whole content..Can anyone please suggest SQL query for this.

rough database structure is below

city_id       city_name       city_order
  1               xyz             1
  2               geh             2
  3               tre             3

Regards,

user2872096
  • 3
  • 1

3 Answers3

3

Add ORDER BY RAND() to the end of your query

Derek
  • 21,828
  • 7
  • 53
  • 61
1

You would do something like this:

select city_id, city_name, city_order from table_name order by rand()

where table_name is the name of your table

DiverseAndRemote.com
  • 19,314
  • 10
  • 61
  • 70
  • -1: worst possible solution. – tereško Oct 11 '13 at 22:53
  • @tereško Please explain why you feel this is the worst possible answer – DiverseAndRemote.com Oct 12 '13 at 01:35
  • Because, unless you are dealing with very small set of data, this method is completely unsustainable. See `EXPLAIN` for more details. Essentially you end up reordering and in-memory table for every request. The time it takes grows at the rate of `n*log2(n)`, which means, that even for a table with 500+ entries, the execution time becomes noticeable, at 50'000 your php script will start timing out. – tereško Oct 12 '13 at 01:58
-1

You can use ORDER BY RAND() like others have suggested, but then you should also take a moment to wave goodbye to any semblance of efficiency and performance that your query may once have had. This is because you're now ordering your result set based on something that is:

  1. Not indexed.
  2. Dynamically generated.

MySQL [and pretty much any other RDBMS] is most decidedly unsuited for nearly any operation that requires introducing randomness.

Better options:

  1. If your anticipated result set is sufficiently small to not cause:

    • Delays based on the bandwidth required between your web and database server.
    • Excessive memory use storing the result set.

    Then simply get all of the potential content items and use array_rand() or shuffle() to get your random selection.

  2. If your anticipated result set is going to be large enough that you don't want to pass the whole thing to PHP then I suggest something like:

    $sql = 
"SELECT COUNT(*) 'count'
FROM content_table
WHERE [your conditions]";

$rs = $dbh->query($query);

$random = rand(0,$rs['count']);

$sql =
"SELECT field1, field2, ...
FROM content_table
WHERE [your conditions]
LIMIT $random, 1";

$rs = $dbh->query($sql);

Both of which will respect your indexes.

Sammitch
  • 30,782
  • 7
  • 50
  • 77
  • 1
    You might benefit from reading [this article](http://jan.kneschke.de/projects/mysql/order-by-rand/) (no downvote .. at least you recognized problem). – tereško Oct 11 '13 at 22:56
  • It worth mentioning that all the solutions provided in the article require data to be without holes, otherwise it won't be evenly distributed or performant. So if you live in the real, not ideal world - the solutions might not provide real randomness – zerkms Oct 11 '13 at 23:06
  • @tereško is a good read. I've got another answer kicking around from ages ago with some thing similar to his holes_map table. – Sammitch Oct 11 '13 at 23:07
  • @zerkms I don't think you've read the hole article. :P – Sammitch Oct 11 '13 at 23:07
  • @Sammitch: I think I do have. Put `1 2 3 10000000` into table and see that the latter will be selected on every run – zerkms Oct 11 '13 at 23:11
  • Which is an edge case. You are trying to dismiss a viable solution based on some made up case, in favor of "solution" that randomly orders **ALL** entries, **before** applying `LIMIT`. It's somewhat sad. – tereško Oct 11 '13 at 23:13
  • @tereško: in other non-edge cases you will see terrible distribution as well. In `1 2 3 7` which isn't that edgy - the `7` will have 4 times more probable to appear than each of `1 2 3`. I don't see how it may be acceptable by anyone. – zerkms Oct 11 '13 at 23:14
  • @tereško: "dismiss a viable solution based on some made up case, in favor of "solution" that randomly orders ALL entries" --- nope, I don't protect `ORDER BY RAND()`, it sucks as well – zerkms Oct 11 '13 at 23:27