Max string value in DB2

[jweir]

Hello,

I need a query in DB2 where it has to select the maximum value in a string field. I have to select two other fields, in addition to the field that needs the max function performed on it. Here is a sample test that we have tried:

Code: Select all

Select MAX(EXPIRE_DATE), CUSTNUM, ZH4IN from RTL.STG_MEDICAL_TABLE
group by (CUSTNUM, ZH4IN, EXPIRE_DATE)

This returns records with the same CUSTNUM with different EXPIRE_DATE fields. I only need one record with the max EXPIRE_DATE.

Any suggestions?

Thanks in advance

[vinothkumar]

Remove EXPIRE_DT from GROUP BY.

[Mike]

Code: Select all

SELECT T1.EXPIRE_DATE,
       T1.CUSTNUM,
       T1.ZH4IN
FROM   RTL.STG_MEDICAL_TABLE T1
WHERE  T1.EXPIRE_DATE = SELECT MAX(T2.EXPIRE_DT)
                        FROM   RTL.STG_MEDICAL_TABLE T2

Mike

[jweir]

Code: Select all

SELECT T1.EXPIRE_DATE, 
       T1.CUSTNUM, 
       T1.ZH4IN 
FROM   RTL.STG_MEDICAL_TABLE T1 
WHERE  T1.EXPIRE_DATE = SELECT MAX(T2.EXPIRE_DT) 
                        FROM   RTL.STG_MEDICAL_TABLE T2

This worked Mike! Thanks a mil!

[ray.wurlod]

Don't you need the maximum expiry date for each CUSTNUM, ZH41N combination? This would need more conditions in the where clause, turning the query into a correlated subquery.

[jweir]

Just for CUSTNUM.