How can I pick a single entry with max value in a column?

Question

I have a table with first name, last name and amount, ordered in descending order by the amount.

I want to get the first and last names in the first row (with max amount).

How can 'MAX' be used in this case? (There is no aggregation; first name + last name are all unique)

Before reading this I thought LIMIT 1 is a good option (because they're ordered) but now you said we can't use it.

Thanks!

Answer 1

Your solution can not assume an order on the table, therefore  you can't use the method suggested by @tzuralon as it is not stable. 

There are other ways to obtain the MAX tuple. 

I'm not allowed to give exact solutions, but here are two hints: #1 use "ALL" , #2 use MAX inside the WHERE clause.

Answer 2

A (weird) bypass is to use a subquery:

SELECT MAX(T.col1), T.col2, T.col3

FROM (<subquery>) AS T

Consistency of col1, col2 to the maximum value is guaranteed only if the table is already ordered (otherwise, it may take the col1,col2 values of the first row, which are not connected to the maximum value) - so it's not the best method, but yet doable.

Comments

I thought about this, but found this solution hacky.
(It's not better in any way than just taking LIMIT 1...)

Amit, is this kind of solution acceptable?

---

I found this:
https://dev.mysql.com/doc/refman/5.7/en/example-maximum-row.html
The first option there works fine;
The only problem is that when 'shop' is a sub query it needs to be written twice- asked about it in http://courses.cs.tau.ac.il/0368-3458/forum/index.php?qa=35