MySQL search query

[rohitbanerjee]

Hello,

 

I can't really explain it so a example will be better:

 

my_table:

 

col1  col2  col3  col4

1      1      23    A

2      1      25    A

3      2      21    B

4      3      24    A

5      3      28    A

 

the query is--

SELECT col3 FROM my_table WHERE col2=SELECT(MAX(col2) FROM my_table WHERE col4='A')

 

For some reason this is illegal, I can also not use a limit because the number of rows is not known a priori. I remember seeing some sort of redirection in some other forum but would appreciate a more explicit response.

 

Thanks

 

This is not legal for some reason -- is there a way

[l0gic]

Try..

 

SELECT col3 FROM my_table WHERE col2=(SELECT MAX(col2) FROM my_table WHERE col4='A')

 

I'm pretty sure you just have '(' in the wrong place.

[rohitbanerjee]

Wow, thanks -- makes me feel like an idiot