Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I try that query I get an error message "Column 'person.Gender' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
–
Paul WhiteDec 5 '12 at 14:40

The interesting thing about that plan is the Job table is only scanned once, despite there being two references to it in the original query. The plan uses an optimization that I call Segment Top. Essentially the execution engine takes advantage of the index order to detect the start of a new group (segment) and take just the first row from each group (top).

Now that we have that result, all we need do is join it back to the Person table: