I am trying to join two tables. I only need the rows in the second table that have the maximum date found in one of the fields it contains. An example would be to pull only rows that have a value of '2007/03/31 07:32' in the field called date modified. The format of the field is HYYMDI. Any help would be appreciated!

quote:

I am trying to join two tables. I only need the rows in the second table that have the maximum date found in one of the fields it contains. An example would be to pull only rows that have a value of '2007/03/31 07:32' in the field called date modified. The format of the field is HYYMDI. Any help would be appreciated!

I assume your question is only wanting the max date value not the actual join. Have you tried the 'MAX.' operator to get the highest date?

No, I DO want the join to pull all row information for that max date. I DO NOT want to pull all the rows back and then decide in the query which one has the max date. Obviously, if I use a Multiple in my join it will pull all rows and not using it will give me the first occurance (or in my case the min date) it finds.

Patch,
I assume that you have a 1-to-many join between the 2 tables.
So, if you use BY HIGHEST 1 date_modified , you should get the row with the maximum value of date_modified .

That doesn't eliminate the joins to all the other rows, it just throws everything out from the matrix that doesn't belong to the HIGHEST 1 record, so performance is going to take a hit. We also do it this way in many cases where I am trying to pull a most recent record for a claim from this history table.
I have two other suggestion which we also use. The first requires actually storing/updating the value of the max timestamp on the associated records in the host file. Requires a bit more maintenance, but saves a ton of time for reporting. The second suggestion is simply to use the BY HIGHEST 1 to get the max record, hold that, and then do a second pass with the JOIN.

Good suggestions. The BY HIGHEST 1 works fine and gives me the max record that I need. I also used the get max record from the 2nd table, joined it to the first table, and then continued on. This worked as well. My wish was I could do something in the actual join statement. Thanks for the suggestions.

There IS such a thing as conditional joins, where the join is only performed if a condition on a field in the host file is met. This wouldn't work in your case, however, because MAX. is only known after the matrix is loaded, not as the records are being read and joined to the crfield.

One thing to watch, though, is that BY HIGHEST 1 does not get translated to SQL, all the rows are returned to WebFOCUS before the HIGHEST 1 is determined.

This

TABLE FILE BASEL_TIME_D
SUM
TIME_DIM_KEY
BY HIGHEST 1 TIME_DIM_KEY
END

produces this SQL

SELECT T1."TIME_DIM_KEY", SUM(T1."TIME_DIM_KEY")
FROM BASEL.TIME_D T1 GROUP BY T1."TIME_DIM_KEY"
ORDER BY T1."TIME_DIM_KEY" DESC
FOR FETCH ONLY;

quote:

That doesn't eliminate the joins to all the other rows, it just throws everything out from the matrix that doesn't belong to the HIGHEST 1 record, so performance is going to take a hit.

Right. This was what I was alluding to. It still performs joins on all the records retrieves everything into the matrix, then FOCUS throws everything except the Highest 1 record out. So if you've got a large DB, you could take a good hit on IOs, CPU time, etc.