Re: Basic SQL question and kudos

On 10/06/2010 12:31 AM, Dennis Gearon wrote:
> I need to get a join across about 8 tables to only return rows for the MAX date in one of the tables for all otherwise identical return results. I am trying to get the most recent edit of normalized records that are composed of elements from all the tables. There could be 1 to 100+ successive edits to the normalized/multi table record. The will be approximately 65,000 total records for now, 100,000,000s later.
>
> If I make one of the values in the select statement be MAX(the date), does that automatically do grouping on 'the date' or will I only get ONE row total returned?

If I understand what you're proposing correctly, I don't think it'll work.

What you need to do is use a subquery to obtain the max of the field of
interest, and filter in your WHERE clause so you only return results
when the selected value is the maximum for that field.

It's hard to be more specific with a fairly general question like this,
but if I'm understanding you right this is how I'd start.

--
Craig Ringer