Unanswered: MAX on date field acts differently on a table
I am trying to pull in provider numbers with their latest tax ID, based upon the D_TAX_EFF field.
I had a similar situation with another table where I needed the most recent description for a code based upon the D_DRG_CANCEL field.
This code worked beautifully on that table:
SELECT * FROM DB2PROD.CRPTDRGCAT
(SELECT MAX(D_DRG_CANCEL) FROM DB2PROD.CRPTDRGCAT)
resulting in only the rows with the most current cancellation date.
But when I run this:
SELECT C_PLAN, I_PROVIDER_NUMBER, I_TAX
WHERE D_TAX_EFF =
(SELECT MAX(D_TAX_EFF) FROM DB2PROD.OPRTPRVTAX )
I only get back the provider number with the most current cancellation date, so only one row! And when I specify a particular provider number, (one I know has more than one tax ID), I get NOTHING because it isn't the provider with the very most recent D_TAX_EFF value.
The purpose of this is to get rid of duplicates and only pull back the most current tax id for a provider.
The structure of the date fields on the two tables is identical.
What am I missing? What do I need to do differently?
Trying to pare down a query result from over 200,000 rows by getting rid of duplicate providers due to the multiple tax IDs.