Mysql select max variable value

Question

I have a temporary table and the following query:

Select *,  @rnkma:=IF(age>17 and age<22,@rnkma+1,@rnkma),
          @rnkmb:=IF(age>21 and age<25,@rnkmb+1,@rnkmb),
          @rnkmc:=IF(age>24 and age<30,@rnkmc+1,@rnkmc),
          @rnkmd:=IF(age>29 and age<35,@rnkmd+1,@rnkmd),
          @rnkme:=IF(age>34 ,@rnkme+1,@rnkme)  from tmp_user where gender='M';

    Select @rnkma, @rnkmb, @rnkmc, @rnkmd ;

The result is:

@rnkma  @rnkmb  @rnkmc  @rnkmd
  5        8       3       4

I want to get max value of this result like:

@rnkmb
  8 

Any suggestions? Thanks.

Answer 1

@Pawel's answer is correct , i will add just one thing as a reference. There's a difference between Max() and Greatest()

MAX() returns maximum value of an expression. Ex:

select max(Age) from staff;

Greatest() returns the greatest of the list of exprs. More than one column must be given.

SELECT GREATEST(150,160,161) FROM XXX;  will return 161

Answer 2

Try using CASE and GREATEST():

SELECT 
CASE GREATEST(@rnkma, @rnkmb, @rnkmc, @rnkmd)
    WHEN @rnkma THEN '@rnkma'
    WHEN @rnkmb THEN '@rnkmb'
    WHEN @rnkmc THEN '@rnkmc'
    WHEN @rnkmd THEN '@rnkmd'
END name,
GREATEST(@rnkma, @rnkmb, @rnkmc, @rnkmd) value
(...)