Welcome to TDBSoverflow, Our class's own StackOverflow. Our rules:
  1. Use only meaningful and self-explanatory titles
  2. Tag your questions with meaningful keywords
  3. Use upvotes and downvotes to rate the answers
  4. When you receive a satisfying answer - Click the "V" button
Remember: you may get up to 5 bonus points to your final grade!

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

+1 vote
94 views

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!

related to an answer for: Using the LIMIT Keyword in Exercise #1
asked Nov 11, 2017 by Assaf (31,090 points)

2 Answers

–1 vote
 
Best answer

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.

answered Nov 16, 2017 by Amit (27,910 points)
selected Jan 15, 2018 by Assaf
+1 vote
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.
answered Nov 11, 2017 by tzuralon (590 points)
edited Nov 11, 2017 by tzuralon
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
...