@ErikDarling Regarding the video, "Efficiently Finding Aggregate Values In SQL Server: Row Mode vs Batch Mode vs Indexes!", the queries after the first one aren't strictly the same because they reject high-rep users with no questions (inner join instead of outer join). But, more importantly, you should find having nonclustered columnstore indexes (on both Users and Posts) performs best and provides great generality.

The simple MAX is the right way to write the query of course. With nonclustered columnstore (just on the columns you need for creation speed) you might need to switch from Results to Grid to Results to Text to avoid SSMS being the bottleneck.

32ms CPU, 40ms elapsed

Custard column store is also an option, but much more intrusive

I realise I'm missing the point of the video

I mean the point about people learning one trick (row number)

oh there's that yeah

@PaulWhite have you ever come across the undocumented OVERRIDE_B*TCH_M*DE_HEURISTICS use hint?

i wonder what happened to the sql workshops guy