Shane O’Neill covers an annoying but necessary thing to remember around window functions:
Now, a new learner of SQL comes along with the requirement to find the last 2 rows per PartitionId.
They are diligent and enthusiastic and have just read about Windows Functions. They think to themselves
Wow! This is great! I can do this with Windows Functions!
They also work for a company that has invested in RedGate’s SQL Prompt so they know that they can rely on SQL Prompt to help iron out any inconsistencies in their script.
So they take the SELECT script above and type in
WHERE… and the auto complete pops up
And that popup isn’t quite accurate… Shane covers this in the guise of a SQL Prompt bug, but it’s a good thing to remember regardless of which tooling you use.