Link to home
Start Free TrialLog in
Avatar of Flashbiter
Flashbiter

asked on

Why does this query produce an error?

This one is really simple folks. I don't have a lot of SQL knowledge, so could you please tell me why this SQL query results in a syntax error? Basically it's a table of bids a user can make for items. Each item has a unique position, but new bids are made every week. What I want is to get a list that tells me for each position the highest bid for that position in a particular week. MySQL seems to have some trouble with the "WHERE" part of the query.
SELECT *,MAX(bid_amount) AS max_bid FROM `bids` WHERE (starting = '2008-07-28 00:00:00') GROUP BY position

Open in new window

Avatar of Kim Ryan
Kim Ryan
Flag of Australia image

What error message are you getting? As you are grouping, you should say
SELECT MAX(bid_amount) AS max_bid
You can only display columns that are grouped, cannot be mixed with all other columns
replace * with your field position

SELECT position ,MAX(bid_amount) AS max_bid
FROM bids
WHERE (starting = '2008-07-28 00:00:00')
GROUP BY position
Avatar of Flashbiter
Flashbiter

ASKER

No that doesn't solve it:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'starting = '2008-07-28 00:00:00') GROUP BY position
LIMIT 0, 30' at line 1
try removing the brackets
WHERE starting = '2008-07-28 00:00:00'
ASKER CERTIFIED SOLUTION
Avatar of Flashbiter
Flashbiter

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial