I had mentioned some new T-SQL functions for SQL Server 2022 and a commenter asked about the difference between Min() and First_value. This post looks at a few cases.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
The Scenario
Let’s set up some data and examine these functions. First a table and some data:
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[MonthlySales](
[TransactionDate] [date] NULL,
[SalesAmount] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
Some data for 2024:
insert MonthlySales (transactiondate, SalesAmount)
values
('2024-01-30', 100),
('2024-02-28', 200),
('2024-03-30', 300),
('2024-04-30', 400),
('2024-05-30', 500),
('2024-06-30', 600),
('2024-07-30', 700)
Now, a query and some results. This query runs the same over() clause for first_value, min, and sum. The sum just shows totals.
It seems that first_value and min do the same thing. Let’s add one item to this. I’ll add two columns that remove the first_value and min from the total sales. Let’s assume we want to get rid of the first month’s sales for some reason (they lag).
OK, this looks good.
The Difference
Now, I’ll add some 2023 sales with this code:
insert MonthlySales (transactiondate, SalesAmount)
values
('2023-01-30', 10),
('2023-02-28', 20),
('2023-03-30', 5),
('2023-04-30', 40),
('2023-05-30', 50),
('2023-06-30', 60),
('2023-07-30', 70)
When we re-run the query, we see different results for lines 3-7. This is because above, we had increasing sales in every line, when ordered by date. In 2023, we have a dip in sales, which happens, so the min value after Mar is 5, but the first_value is still 10 (from Jan).
The same sort of issue can come with last_value and max, as the ordering might not match the sorting of values.
At first glance, these might seem like duplicate functions, but that really depends on the use cases you have for windowing functions and your data. You might often order by a numeric used in an aggregate, in which case they can be the same. However, there are plenty of cases where these work differently.
Another quick example: in my baseball database we can see Barry Bonds HR counts by year, and the min differs from the first year in SFN.
SQL New Blogger
This post took me about 15 minutes to setup and write. I’ve practiced telling a story, but I bet most of you could produce this in 30-45 minutes of work. Easy if you spend 1 hour a week on your career branding.
Showcase your skills and set up a blog today.