SELECT MAX() function in MySQL

Question

I'm trying to figure out a problem I'm having with a SELECT MAX() command. I want to gather the highest value in the 'BidAmount' column, but I only want selected entries to be scanned for the highest value.

For example, I want the highest 'BidAmount' to be returned where another column 'UserID' = 6. I want to know the highest 'BidAmount' for a specific customer (so the column 'UserID' will equal '6').

I tried using this:

SELECT MAX(`BidAmount`) FROM `requests` WHERE `UserID` = 6

But it doesn't work. If the above SQL code should work, it might be a problem with my PHP. What shall I do?

Answer 1

Your SQL statement is fine. What do you mean by doesn't work? Please show us sample data, and actual vs. expected output from your query.

Update

It sounds like you may have an incorrect data type for BidAmount and are getting a non-numeric sort. Can you tell us what that data type is?

Answer 2

SELECT MAX(BidAmount) FROM requests WHERE UserID = 6 GROUP BY UserID

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Answer 3

Hi,

The sql seems fine, tested just to be sure, so I see 3 possible problems:
- you mistype one of the column names or the table name
- UserID is not number
- the problem is in your app code

You can try to execute the query in a phpmyadmin or toad for mysql, to see the error message if there is...

Good Luck ;)