selecting all row of max value of a column I have this simple query.

SELECT 

    t3.C_COMDTY_SHORT_DESC
    ,t3.C_HS_CODE    
    ,t3.I_COMDTY_ID
FROM

    ADM_COMMODITY_MASTER t3 
WHERE
 t3.C_HS_CODE = '2203.00.00' 


which return two rows as below.



Now i want to select the row of corresponding the column which has max I_COMDTY_ID.
so i write like this.

SELECT 

    t3.C_COMDTY_SHORT_DESC
    ,t3.C_HS_CODE    
    ,t3.I_COMDTY_ID,max(I_COMDTY_ID)
FROM

    ADM_COMMODITY_MASTER t3 
WHERE
 t3.C_HS_CODE = '2203.00.00'  group by t3.C_HS_CODE;


This is what is got.



Why column C_COMDTY_SHORT_DESC value is BEER ? I know how to get max row and other way around. But why it is not working ?
Your query will get the max value of I_COMDTY_ID with ANY combination of the other fields from the rows that satisfies your WHERE condition.

A better way to do that:

SELECT 
    t3.C_COMDTY_SHORT_DESC,
    t3.C_HS_CODE,
    t3.I_COMDTY_ID,
    I_COMDTY_ID
FROM
    ADM_COMMODITY_MASTER t3 
WHERE
    t3.C_HS_CODE = '2203.00.00'  
ORDER BY 
    I_COMDTY_ID DESC 
LIMIT 1;