I am the host for T-SQL Tuesday this month, and I hope that a lot of people like the topic. This idea actually came to me earlier this year when I happened to see someone ask about a T-SQL problem and get an answer using a Window function. This person mentioned they hadn’t used the window function before, and I wondered how many people haven’t even tried using the OVER() clause with a window function.
I also saved the idea of window functions just in case I didn’t have a host, and I realized a few months ago November was blank. So I created an invitation for technical solutions using window functions, hopefully mature solutions you’ve use many times.
If you want to host, contact me, and send me your blog link and I’ll get you scheduled. FYI, I’m looking for people in the second half of 2024, so it’s not an immediate need.
Cleaning Up Window Functions
I don’t write a ton of code, and I don’t have any really cool solutions, but I did want to highlight one thing from SQL Server 2022: the WINDOW clause.
In the past we’ve often had code like this:
WITH HRCTE
AS ( SELECT hrorder = ROW_NUMBER() OVER ( PARTITION BY p.franchName ORDER BY HR DESC )
, p.nameFirst
, p.nameLast
, p.franchName
, p.HR
FROM dbo.Players p
)
SELECT hrdenserank = DENSE_RANK() OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
, hrrank = RANK() OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
, playercound = COUNT(p.nameLast) OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
, hrsum = sum(p.HR) OVER ( PARTITION BY HRCTE.franchName ORDER BY HR DESC )
FROM HRCTE
WHERE HRCTE.hrorder <= 5;
That’s not bad, but I’ve written a bunch of repeating code in the OVER() clauses. In SQL Server 2022, I can do something more like this (just the outer query):
SELECT hrdenserank = DENSE_RANK() OVER frname
, hrrank = RANK() OVER frname
, playercound = COUNT(p.nameLast) OVER frname
, hrsum = sum(p.HR) OVER frname
FROM HRCTE
WHERE HRCTE.hrorder <= 5
WINDOW frname AS ( PARTITION BY HRCTE.franchName ORDER BY HR DESC );
That, to me, is a cool maturity of the Windowing function capability in T-SQL. I can alias a window and reuse it in my code. This also makes I can make a few different windows and easily see which one is used with which aggregate.