>Thanks for replying Walter, that's a good one as well.
>
>That second one is particularly interesting.
>
>Any time I'm dealing with rate effective dates, my mind almost instinctively goes for the MAX approach, but people now have 3 different versions to choose from. 3 of the last 4 DB projects I've been on, had to deal with dates in this fashion, and I imagine others have to deal with this frequently.

I've learned to avoid the MAX() strategy because it is has too many problems. First of all, you can only use this strategy only if you are determining the record based on only one field (For example what would you do if you have to select on a date and a seperate time field ?). Second, in VFP this is not as optimizable and arguably in SQL another stategy might work better.

The LEFT JOIN, WHERE ISNULL strategy is particulary usefull if you have a LEFT JOIN on the table you want to filter (Replace the first join with a left join). You can't use a MAX() strategy nor the NOT EXISTS() strategy here.


The EXISTS() clause is often overlooked. In all cases where you only want one particular record in a group of records, the EXISTS() clause can help you out. I can't count the times that people where creating difficult constucts to get the most recent record for a particular item while a simple construct like

SELECT * FROM Items A WHERE NOT EXISTS(SELECT 1 FROM Items B WHERE B.Date > A.Date)

would do the trick perfectly.