MYSQL Query - order by and then select random of the same value

Question

I'm currently looking in MySQL to order results by price, and then output a random one with the highest price. (several will have the same price)

I've had a look at other stackoverflow questions and found people with answers saying that if two results are the same you cannot guarantee which one will be outputted however that sounds like a bit of a glitch/hack, is there anyway to order a table by the highest results and then chose a random one of those results?

$qry="SELECT * FROM campaignInformation ORDER BY campaignInformation.bidPerCustomer DESC LIMIT 1";

two of my "bidPerCustomers" are 0.25 and I would like to to chose a random one of these every time, however not choose one with a bet of 0.11 for example

Thanks in advance guys! I'm asumming that I will have to make a query and then choose a random one from the results however it would be nice if I could do it in the query to begin with.

ORDER BY campaignInformation.bidPerCustomer DESC, RAND() LIMIT 1 doesn't work ? — Jeremy
not possible with just limit, since it doesn't care WHAT the results are, just that you only want ONE of them. — Marc B
@Jeremy I've never tried that, I'll give it a quick go however I assumed you could only have one order by per query — ashlewis
no, you can use multiple order : ORDER BY field1 ASC, field2 DESC, .... — Jeremy

Saeven Saeven · Accepted Answer · 2015-08-05T17:00:08

If you just want to select any from those with max value, then you can build it up cleanly:

SELECT * FROM campaignInformation C WHERE C.bidPerCustomer = ( 
    SELECT MAX(D.bidPerCustomer) FROM campaignInformation D
)

That'll net you all rows that have max value. You can then select one from that table, LIMIT 1, order by RAND()

SELECT * FROM (
    SELECT * FROM campaignInformation C WHERE C.bidPerCustomer = ( 
        SELECT MAX(D.bidPerCustomer) FROM campaignInformation D
    )
) AS X
ORDER BY RAND()
LIMIT 1

Every derived table needs an alias, hence the 'AS X'

Make sure you have an index on your bidPerCustomer column, or havoc ensues.

MYSQL Query - order by and then select random of the same value

1 Answers