Determine the field in ORDER BY RAND() in mysql

Asked

Viewed 7,411 times

4

Determine which field of the table will have ORDER BY RAND()

Example: display random data according to field (id_category)

As I put one: WHERE ORDER BY RAND() id_categoria ???

php mysql

  • I don’t get it. There’s no such thing as Rand from a field, RAND() will simply generate any random number.

  • The problem is in the syntax?

  • Yes! You can determine a table field when using ORDER BY RAND() ?

  • 1

    What is the result you want? Why do you need to randomize the id of a field? Using any random number for order wouldn’t give in it?

  • To "show data randomly", do the query in the database without ORDER BY and use the function shuffle() PHP to randomize the result. (I write this based on the tag php of the question).

  • 2

    It would be nice to explain a little better what you want in the question. Perhaps, explain verbatim what you want to actually happen in the results, instead of just explaining the query you’re trying to.

Show 1 more comment

2 answers

6

According to the documentation random results cannot be obtained by a given column.

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column Multiple times. However, you can Retrieve Rows in Random order like this:

mysql> SELECT * FROM tbl_name ORDER BY RAND()

You cannot use a column with RAND() values in an ORDER BY clause, as ORDER BY would evaluate the column multiple times. However, you can recover rows in random order like this:

mysql> SELECT * FROM tbl_name ORDER BY RAND()

  • Just to add: not only is "not possible", as the documentation says, but I can’t see why it would be necessary :)

  • Along with a limit in a subquery we can extract random categories from another table. Ex: SELECT * FROM produtos WHERE categoria_id IN (SELECT categoria_id FROM categorias GROUP BY categoria_id ORDER BY rand() LIMIT 5). Only use case I could imagine :)

  • It’s definitely valid, but I wanted to see a real case for this :p

1


Let’s say your table has this structure: 

CREATE TABLE `cliente` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(50) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1$$

It would look like this, because I want it to only do Rand() at id

SELECT a.id, b.nome from (SELECT id FROM cliente ORDER BY rand()) a join cliente b on b.id = a.id

In your case (for example by completely ignoring its structure)

SELECT a.id_categoria, b.* FROM (SELECT id_categoria, a.id FROM tabela ORDER BY rand()) a join tabela b on b.id = a.id

  • But does it make a difference to stop using the subquery and simply use ORDER BY RAND() in the outside query? I don’t think it does.

  • makes difference just performed @bfavaretto test

  • 4

    What difference? If it is in order, each execution will give a different order because it is Rand.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.