Selecting an attribute that has max value for another attribute

**** Edit: How do I also consider the case where there is more than one property with the highest value?

I’m trying to write an outer query that takes the propertyname with the highest value from an inner query that produces a table with propertyname and value as its columns.

The inner query gets me something like this:

propertyname | value  |
-----------------------
house1       | 300000 |
house2       | 400000 |
townhouse1   | 200000 |
townhouse2   | 300000 |

I want to be able to output the propertyname that has the highest value. e.g. house2 in this example.

I’ve tried doing this:

SELECT propertyname, MAX(value) FROM
  (inner query result - this would be the table shown above)

However I get an error doing this, which is as follows:

“#1140 – In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘averages.stname’; this is incompatible with sql_mode=only_full_group_by”

My desired output is just the propertyname.

I’ve only been introduced to sql in the past few weeks so my understanding of the query language is quite minimal. Any help would be appreciated.

inner query is:

(SELECT propertyname, value FROM property P, propertylisting P
WHERE P.propertyid = PL.propertyid

UNION ALL

SELECT propertyname, value FROM property P, sharedpropertylisting SPL
WHERE P.propertyid = PSL.propertyid)

There is one table to list all the properties, and then one table to list the value of shared properties (e.g townhouses) and one to list the value of stand-alone properties (e.g. houses).

Answer

Use order by and limit:

select t.*
from (select . . . ) t  -- your query here
order by value desc
limit 1;

If you want all values with the same maximum, then you probably need to repeat the query:

select t.*
from (select . . . ) t
where t.value = (select value
                 from . . .
                 order by value desc
                 limit 1
                );

Advertisement

Answer