Window Functions In WHERE Clauses

Kevin Feasel

2018-04-20

Syntax

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.

Related Posts

Joins When No Join Types Are Valid

Hugo Kornelis has a brain-teaser for us: The query below can be executed in any version of the AdventureWorks sample database. Don’t bother understanding the logic, there is none. It is merely constructed to show how SQL Server handles what appears to be an impossible situation. 1 2 3 4 SELECT          d1.Name, d2.GroupName FROM            HumanResources.Department AS d1 FULL OUTER […]

Read More

Implementation Matters: CTEs In Postgres And SQL Server

Brent Ozar looks at a couple of places where Postgres and SQL Server differ in implementation details: In SQL Server, if you write this query: 1 2 3 4 With AllPosts AS (SELECT * FROM StackOverflow.dbo.Posts) SELECT *   FROM AllPosts   WHERE Id = 1; SQL Server builds a query plan for the entire operation at […]

Read More

Categories

April 2018
MTWTFSS
« Mar May »
 1
2345678
9101112131415
16171819202122
23242526272829
30