Here is a query used for DB2 (AS/400) where I am trying to retreive an account that has the newest effective date (EffDate) and within that, the highest endorsement number (EndsNum):

Select Account, EffDate, EndsNum, Name
From TABLE_A
Where Account = '123456789' AND
EffDate = (Select Max(EffDate) from TABLE_A where Account = '123456789') AND
EndsNum = (Select Max(EndsNum) from TABLE_A where account = '123456789' AND EffDate = (Select Max(EffDate) from TABLE_A where Account = '123456789'))

Works, but this seems like the wrong way to go about this...any suggestions for improving the query?

Instead of using subqueries, would ordering the records in the correct order and just selecting a single record work? Something like:

SELECT Account, EffDate, EndsNum, Name
FROM TABLE_A
WHERE Account = '123456789'
ORDER BY EffDate DESC, EndsNum DESC
FETCH FIRST 1 ROWS ONLY

A query like this should have the maximum EffDate as the first record and the maximum EndsNum associated with that date.