2

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.

enter image description here

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.

enter image description here

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 ?

1
  • Please let me know if the table structure is required. Sep 23, 2016 at 13:51

1 Answer 1

2

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.