Page 1 of 1

Use a second where clause after 'MAX()

Posted: Wed Oct 19, 2011 12:40 pm
by phillyrob
I can run this query SELECT * FROM count2 WHERE numb2 = (SELECT max( numb2 )FROM count2); and get a result.

I would like to throw in a second where clause in there like this:

SELECT * FROM count2 WHERE numb2 = ( SELECT MAX( numb2 ) FROM count2 WHERE Ball = 1 )

When I do this however, It will not isolate only the MAX(numb2 ) where Ball = 1 ; it brings up multiple other numbers other than Ball = 1.

Is there a way to do this?

Re: Use a second where clause after 'MAX()

Posted: Wed Oct 19, 2011 4:15 pm
by VladSun
Without checking it, it seems that you need table aliases:

Code: Select all

SELECT * FROM count2 WHERE numb2 = ( SELECT MAX( derivate_count2.numb2 ) FROM count2 as derivate_count2 WHERE derivate_count2.Ball = 1 )
Otherwise you are mixing the select and subselect table columns.

Re: Use a second where clause after 'MAX()

Posted: Thu Oct 20, 2011 8:00 am
by phillyrob
Thx VladSun, I tried your suggestion, but it is still pulling the same data. It is not isolating the number in the where clause. I am still getting other numbers besides where Ball = 1. But thx anyway.