Topic: [SOLVED] selecting highest field

[rempires]

hmm, okay so i have a query and i want to select the eventName based on the biggest number, but MAX apparently can't be used in the WHERE clause, any help, here is what i attempted

Code: [Select]

SELECT `eventName` FROM events WHERE `addedToScheduleCurr` = MAX(`addedToScheduleCurr`)

any idea on how else i could do this

thanks,
john

p.s.
phpMyAdmin error says
#1111 - Invalid use of group function

[drranch]

Code: [Select]

SELECT eventname, MAX(addedToScheduleCurr) FROM events GROUP BY eventname

[rempires]

that returns all the events with different names, and if i group by addedToScheduleCurrthen it returns all of the addedToScheduleCurr with different number.  I'm attempting to only retrieve the highest addedToScheduleCurr event name and avoid using 2 sql statementS such as

SELECT MAX(addedToScheduleCurr) FROM events

and then
SELECT eventname, FROM events WHERE addedToScheduleCurr = '$resultFromPreviousSQlStatement'

[fenway]

Why can't you just add the where clause the the posted code?

[rempires]

mysql returns an error when MAX() function is used in the WHERE statement

hmm I'm not quite sure if everyone is understanding what I'm asking either so i'll try to re-explain it

lets say i have a table that looks like

eventName    addedToScheduleCurr

event1              10
event2              9
event3              28
event4              15
event5              19

i need an sql statement that will  pull out "event3" becuase of the fast that it has the highest value in the addedToScheduleCurr column

eventName    addedToScheduleCurr

event1              15
event2              7
event3              9
event4              35
event5              42

in the above example it would pull out event5 because it has the highest addedToSceduleCurr column

i'm starting to think this might have to be 2 separate quires...

[rameshfaj]

I think MAX() requires group by clause also, try that.

[rajivgonsalves]

you don't require a group by your query should be

Code: [Select]

SELECT `eventName` FROM events WHERE `addedToScheduleCurr` = (select MAX(`addedToScheduleCurr`) from events)

[fataqui]

You can also do it like this...

Code: [Select]

SELECT eventName FROM events ORDER BY addedToScheduleCurr DESC LIMIT 1;

[rempires]

thanks everyone, that was exactly what i was looking for!

john