selecting all row of max value of a column

Question

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 ?

Answer 1

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;