MySQL - how do I find the row with greatest value

Anon

I need to select the one row (or rows if there are multiples) with the largest value in that column.

It seems like this would be easy but I can't get it to work.

I'm trying this:

SELECT MAX(amount) as maxvalue FROM table_name WHERE cat_id = myvariable GROUP BY amount

Also, can someone please explain to me the difference between ORDER BY and GROUP BY. I don't quite get when you would use GROUP BY.

Thanks.

Anon

That's not valid SQL at all. You are trying to group by a value that you are not even selecting. It looks like you could simply remove the group by and you would get your answer.

Anon

According to the MySQL manual (and mysql itself since it whines when I take GROUP BY out) MAX is a function that works in conjunction with GROUP BY.

I guess the problem is I don't understand GROUP BY.

I just want to select the row(s) that have the greatest value in the one column. What is the SQL statement to do that? Can someone show me examples.. And a nice explanation of GROUP BY. The MySQL manual is kind of cryptic.

Anon

It's not clear yet exactly what it is you are trying to do. By that I mean, every time I started to write a sample query for you, I came up with several different alternatives.

For example, just for starters, it doesn't make sense to combine MAX and GROUP BY in the same query. MAX really does mean maximum, so it can't give you several different maxima for the table. Your original query says:

ELECT MAX(amount) as maxvalue FROM table_name WHERE cat_id =
myvariable GROUP BY amount

and it means

Looking at only those rows where cat_id = myvariable
find the largest existing value of the column 'amount'
and return that value.

At this point there's nothing to GROUP BY as a MAX() query of this type should only ever return a single row. It's not saying "return all rows whose value for 'amount' equals the maximum", it's saying "return the maximum <b>value</b> for all matching rows".

Anon

Ok, so let me get this straight here, you wish to select the largest value from a specified column, is that what you're after? If so then it's a simple select/limit task, and you don't need MAX either...

Say we have the following table:

| Index | Value |

| 1 | 1 |
| 2 | 90 |
| 3 | 10 |
| 4 | 80 |

| 5 | 2 |

Then we'd do a select something like so:

"select Value from tablename order by value desc limit 0,1"

Or your might be able to use:

"select MAX(Value) from tablename"

This would get the largest value from the column "Value"

As for a tutorial on MAX and GROUP BY, sure I got a simple on fer ya...

Basicly all MAX does is returns the largest value from a column, by default it returns only one value. Now if you use GROUP BY then you can have it return one value per different value in the column specified.

For example:

| Age | Value |

| 3 | 1 |
| 10 | 90 |
| 4 | 10 |
| 3 | 80 |

| 10 | 2 |

Run this query on the previous table:

"SELECT MAX(Value) from tablename"

you would get the result: 90;

Now run the query with GROUP BY:

"SELECT MAX(Value) from tablename GROUP BY Age"

And you would get three results, one for each different age: 80, 90, 10

Of course in real life you'd want to also have it return the age values as well just to keep things together, as well you'll probably want the ages to appear in cronological order from youngest to oldest (or vice versa) so you'd toss on ORDER BY, so lets add to it just a bit:

"SELECT Age, MAX(Value) from tablename GROUP BY Age ORDER BY Age DESC"

I've tested all these queries on my copy of MySQL (Ver 3.23.26) directly so they do work, FYI.

Hope this answered any questions you had on the subject, oh and don't feel too bad the manual confuzzles me for the most part too. I actualy just picked up a copy of "MySQL & mSQL" from O'Reilly for easy reference purposes just so I can easliy solve problems like this.

Anon

Hey Shaun,

Thanks for the example. I understand GROUP BY a little better now. However as I have realized this isn't going to help what I want to do.

I DO want to return the largest value in a given column, but I want it to return that entire row. So drawing from your example (which I should have given one to begin with to avoid confusing people)

| Age | Value | Name |

| 3 | 1 | Fred |
| 10 | 90 | Joe |
| 4 | 10 | Sandy |
| 3 | 80 | Keith |

| 10 | 2 | Jennifer |

I would want to select not only the value 90, but the age 10 as well and the name Joe as well (I'm doing this with PHP obviously so I'd access them via mysql_fetch_object())

Is there any way to do that without parsing through the whole table and keeping track of which row has the highest value on the column I'm searching?

Anon

This requires a subquery--which mysql doesn't have, so you have to emulate it with two queries, the first to derive the max() and the second that selects all rows where the target column matches the max value.

Anon

Actually, after looking back over what Shaun said, I think I can do it using LIMIT like he first showed.

I just order by the value I want the largest item of, and then limit it to one column.

I didn't pick up on that earlier. Doh!

Anon

Doh indeed. Although, on second thought, hasn't he said that if several rows happen to have the max value, he wants them all? In that case, the LIMIT won't do any good because you won't know that the value should be.

Anon

I thought I showed you that already, oh well to recap:

"SELECT MAX(Value), Age, Name from tablename GROUP BY Age ORDER BY Age DESC"

This will return the Age, Value, and Name of each person with the largest value from each Age group. Can you see the slight addition, I added Age and Name to the Select statement. I'm not sure if this will return 2 items if the MAX(Value) is held by two items or not so give it a try. I'd do it myself but I'm in the middle of a pretty harsh project right now that I have to get back to.

Anon

"SELECT MAX(Value), Age, Name from tablename
GROUP BY Age ORDER BY Age DESC"

produces the error message "Illegal use of aggregates or non-group column in target list". Removing 'name' from the select list fixes the problem, but now 'name' isn't one of the output fields. Adding 'name' to the group-by specs isn't a complete solution, either, since now you get the following results:

max|age|name
---+---+--------
 2| 10|Jennifer
90| 10|Joe
10|  4|Sandy
 1|  3|Fred
80|  3|Keith

In other words, it's producting a separate max() subtotal for each unique combination of age+name, which is hardly what is desired.

It's beginning to look like multiple queries to me:

create temporary table x (maxval int, age int ) select
   max(value) as maxval, Age from tablename group by age

select tablename.* from tablename, x where tablename.age = x.age and   &nbsptablename.value = x.maxval order by ...

Anon

Weird, I wonder why it lets Age through and not Name... I'll have to look into this in more detail.

Anon

Each column in the results needs to be either an aggregate function (e.g. max) or else used in the GROUP BY statement.

Anon

Nope, I was right, it works just the way I said just fine on MySQL 3.23.26,

I used this query using phpMyAdmin's query box and got the exact results that I was expecting.

SELECT max(views),uin,quote from quotes group by uin

This will return the values max(values), uin, quote for each item with a different uin.

Thou as the man said it still won't give back more than 1 result for each value from the max statement, but I still wanted to get this part straight.

Anon

Scott...

Simple:

SELECT * FROM <table_name_here> ORDER BY <column_holding_max_value> DESC LIMIT 0,1

Cheers!

SharkPosters.Com