Select Random Max in MySQL

I have this table:

id    name    bid
1     Test1   5.50
2     Test2   5.50
3     Test3   5.49

I want to select 开发者_JS百科the row with the highest bid. If the highest bid is equal on another row, then it should randomly select one of the highest bid rows.

I tried:

SELECT name,max(bid) FROM table ORDER BY rand()

The output:

id    name    bid
1     Test1   5.50

My problem is that id "2" is never displayed because for some reason my query is only selecting id "1"

---

SELECTing name and MAX(bid) in the same query makes no sense: you are asking for the highest bid aggregated across all the rows, plus a name that's not aggregated, so it's not at all clear which row's name you'll be picking. MySQL typically picks the “right” answer you meant (one of the rows that owned the maximum bid) but it's not guaranteed, fails in all other databases, and is invalid in ANSI SQL.

To get a highest-bid row, order by bid and pick only the first result. If you want to ensure you get a random highest-bid row rather than just an arbitrary one, add a random factor to the order clause:

SELECT name, bid
FROM table
ORDER BY bid DESC, RAND()
LIMIT 1

---

SELECT name,bid
FROM table
WHERE bid=(SELECT max(bid) FROM table)
ORDER BY RAND()
LIMIT 1

should do the trick. Waiting for more optimized request ^^

---

That's because you're using an aggregate function, which collapses everything into a single row. You need a sub-select:

SELECT *
FROM table
WHERE bid = (SELECT MAX(bid) FROM table)
ORDER BY rand()
LIMIT 1;

But also be aware of why not to use ORDER BY RAND(). Although if you have only a few results, the performance implications may not be significant enough to bother changing.