[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

 

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]

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

 

 

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

[fataqui]

You can also do it like this...

 

SELECT eventName FROM events ORDER BY addedToScheduleCurr DESC LIMIT 1;

[rempires]

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

 

john