Use a second where clause after 'MAX()

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
phillyrob
Forum Commoner
Posts: 34
Joined: Wed Jun 18, 2008 12:00 am

Use a second where clause after 'MAX()

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
phillyrob
Forum Commoner
Posts: 34
Joined: Wed Jun 18, 2008 12:00 am

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

Post 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.
Post Reply