Time to read: ~ 2 minutes
I meant to talk about this a while ago but got caught up in different projects so I’m only getting around to it now.
In most of the cases with Redgate, I appreciate what they do because it is a great learning tool for Accidental DBAs or those just learning SQL Server.
Then I found a problem…
First, let us set up our environment to show what I’m talking about. Let’s use the tempdb database and create a dummy table with 1000 rows.
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
Our intrepid learner selects that “LastN” option and SQL Prompt auto completes the line to…
They run that code and they get…
Msg 4108, Level 15, State 1, Line 26
Windowed functions can only appear in the SELECT or ORDER BY clauses.
(4) WHERE **
(5) GROUP BY
(6) WITH CUBE or WITH ROLLUP
(8) SELECT **
(10) ORDER BY
However, the main points are that (1) they don’t yet know this, and (2) they have unfortunately been steered wrong by their tools.
Who you gonna call?
In this case it’s easy, log onto Redgate Hub, head to the Product Forms and then to the SQL Prompt section and log a bug report.
I cannot wait until this little leak is locked up!
If you want to know a way around this, then I’d normally use a CTE…