Inner Join Max Value in table MYSQL (PhpMyadmin)?

0
votes

i'm trying to write a MySQL query using phpmyadmin, I need to take the Maximun 'Site' In 'Value1' but, with the condition of having 'Value2'>10 OR 'Value3' > 10.

In this image i show the Expected Result.

Expected Result of Query. CLICK Thanks.

I Try this, but it does not work

SELECT Value1,Value2,Value3,Value FROM mytable b1

WHERE Value2= ( SELECT MAX( b2.Value2) FROM mytable b2 WHERE b1.Value1=b2.Value1)

Please, i nedd a general scheme to solve this problem.

1
phpmysqlsqldatabasephpmyadmin
Where is your query attempt? – Sean
Hint: look into post aggregation filtering – Mihai
What would happen if there's multiple values in Value1 which are equal to the maximum? – apokryfos

1 Answers

1
votes

You could do something like:

SELECT t1.Site, t1.Value1, t1.Value2, t1.Value3 
FROM Table t1
WHERE Value1=(
      SELECT MAX(t2.Value1) 
      FROM Table t2 
      WHERE t2.Site = t1.Site
) AND t1.Value2 > 10 AND t1.Value3 > 10;

If you have mutliple values in value1 which are equal to the maximum, you'd get them all as long as the value2 and value3 conditions are maintained.

If you don't want that then GROUP BY Site at the end or select DISTINCT Site.