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

Asked

Viewed 344 times

0

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

Example:

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

However, I would like that, if more than one user fell in the same question of ordering (for example, the user has the same city and position in this ordination), the ordination was 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 marry the ordination of the cargo and cidade simultaneously. However, the criterion that the MYSQL will use after this ordering, wanted between these two (when they are identical) the result is random.

That is, 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 that in the MYSQL?

mysql classification random

  • Qnd vc behind the database the fetch array in variavek is not so $query[0]['name']. You could make use of the hand with the limit of queries example has 10 queries vc makes a $aleatorio= hand(0.9). And when I would use the array I would put it in the $query [$randomly]['name']. And if you do not know the limit of each query has a function that tells the size of the array or uses Count I do not remember well.

  • @Thallesdaniel did not understand anything. I use the Laravel for this, I do not use the PDO pure or the mysqli_. But the question is not related to PHP but with MYSQL.

  • Your query in the database returns a correct array?

  • Yes. You’re not suggesting I use a shuffle in the array, right? Because it would invalidate my ordering from the bank. As I said, I want the solution ONLY in MYSQL.

  • you do not that when office and city is equal do a hand in the correct name?

  • I believe it is easier to bring the consultation and then make a hand with the consultation in the position condition and city are equal.

  • Wallace, I don’t know the possibility of this intelligence in Mysql. We need to analyze all the points. Why do you need this? How do you want the data output? Why can’t the data output be as is mysql output? I see no use for the data to come out like this. Also remember the responsibility of data, sometimes not everything is in charge of DBA and yes of dev.

  • You can do yes, I found out. I’m only analyzing now which of the ways will be the best to post here

Show 3 more comments

1 answer

4

I ended up discovering that it is some very simple. It follows the same reasoning of the question How to sort the data of a query by predefined values?.

An important issue is that the ORDER BY, when used with more than one sort condition, it works in the same way as an alphabet sort, when two letters coincide with A: She leaves for sorting from the next letter.

Example:

 ABC
 AZX
 ADC

The ordering 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 sort to be random, just add the function RAND as the third ordering factor.

This will stay that way:

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

    Beware of the cost of performance when using these conditions.

  • You warned me. http://answall.com/questions/87450/por-que-order-by-rand-deixar

Browser other questions tagged mysql classification random

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