Is there any way to define a random order by order for an already established order?

Question

Is there any way to define a random order by order for an already established order?

Navigation

#1 by (4 votes)

0

If the title of my question was confused, I explain: I want to sort a result of a given query from MYSQL .

Example:

SELECT * FROM 
     usuarios
ORDER BY 
    cidade = 'BH' DESC,
    cargo = 'Programador' DESC

But I would like that if more than one user falls into the same ordering order (for example, the user has the same city and the same position in that order), the ordering is random.

Example:

|   nome    |   cargo       |   cidade  |
-----------------------------------------
| wallace   |   programador |   bh      |

| helbert   |   programador |   bh      |

| simão   |   programador |   sp        |

| miguel    |   venda       |   sp      |

In the above example, wallace and helbert match the ordering of cargo and cidade simultaneously. However, regardless of the criteria that MYSQL will use after this sort order, I wanted the result to be random between those two (when they are identical).

In other words, if the results of ORDER BY result in the same amount of items that match the ordering, I want the next ordering between them to be random (one hour wallace will be first, another time may be helbert ).

How do I do this in MYSQL ?

mysql random

asked by anonymous 17.09.2015 / 14:41

1 answer

Return image value Write MYSQL data in pt-br / UTF8 format

score 4 · Answer 1

I ended up finding out that it's some simple good. It follows the same reasoning of the question How to sort the data of a query by default values? .

An important question is that ORDER BY , when used with more than one sort condition, works the same way as a letter order of the alphabet, when two letters start with A : It starts with order from the next letter.

Example:

 ABC
 AZX
 ADC

Sorting by letters would be

ABC
ADC
AZX

This is because of the second criterion (which is the second letter of the word).

The same thing happens with 'ORDER BY.'

Since I want values of the same ordering to be random, just add the RAND function as the third sort factor.

This will look like this:

SELECT * FROM 
     usuarios
ORDER BY 
    cidade = 'BH' DESC,
    cargo = 'Programador' DESC,
    RAND()