Why can't the mysql aggregate function max find the largest value in the group?

clipboard.png
the demand is to find the name with the highest price of danwei.
I use aggregate function to add groups, which is useless, not only MAX is useless, MIN is not available, except COUNT can work.
does the Great God have any other ways?
looked up a lot of information and said that it could be done in a way similar to the following, but not:
select ab.danwei,ab.price from film as ab inner join (select danwei,max (price) from film group by danwei) as bb on ab.danwei=bb.danwei and ab.price=bb.price;

Mar.31,2022

clipboard.png

clipboard.png

select max(price),id,danwei,name from (select * from test order by price desc) a group by danwei;


clipboard.png


I guess the type of your price is string, not a number.

change the price type to int, or use the case function to convert price to a number.

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b43348-416b6.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b43348-416b6.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?