FIRST_VALUE vs. Min: #SQLNewBlogger

Posted on December 4, 2024 by way0utwest

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

GO

SET QUOTED_IDENTIFIER ON

GO


CREATE 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.

2024-11_0108

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).

2024-11_0109

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).

2024-11_0111The 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.

2024-11_0113

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.

Posted in Blog | Tagged , , | Comments Off on FIRST_VALUE vs. Min: #SQLNewBlogger

Reducing the Cycle Time

Posted on December 4, 2024 by way0utwest

There are lots of software development methodologies. This page lists a few, among them waterfall, agile, iterative, rapid, and more. What’s been interesting to me is that the process of deciding what to code and then whether it works doesn’t change much between different ways of building software.

Instead, the cycle time between when we ask a client what to do and when we deliver it changes. The more agile/lean we are, the lower the cycle time. The more waterfall-ish, the larger the cycle time. I guess that analysis and breakdown of problems into work also changes, as the scope in modern DevOps styles of development is smaller (more contained) than in waterfall.

However, we seem to follow the same steps. In the database world, we might do similar things if we think about how we build data models and code systems. We could get all the requirements and build the entire model, or we could get some requirements in an area, build that, and then ask for more. The former is more of a waterfall approach and the latter more agile/DevOps-y.

Is one better? Not really. I would say they are both situational. In some domains, waterfall might work better. When deciding to build a system to launch rockets, most of the problem domain is known and not changing often, so waterfall type approaches likely work well. Certainly we still went some level of decoupling to take advantage of changes that do occur, primarily in the hardware, but the overall problem remains the same.

However, in many of the business or software tooling places I’ve worked, no one has a good grasp of the entire domain. Heck, I think in most businesses, people roughly know how business runs, but they forget the myriad of exceptions that ensure our environments look chaotic to software, and they often constantly refine (or re-direct) the way the business works in pursuit of their latest goals. At times I’m amazed business runs smoothly, though I think this just shows how much we tolerate variance in business processes that we think are more set and defined than they are in reality.

I am a big believe in loose coupling and accepting uncertainty. I hope for the best, but plan for the worst, or at least, plan for things to change. I like NULLS in databases, not everywhere, but in places because have unknown values. I like agile/DevOps approaches to software because we rarely know all the information about a problem. Heck, sometimes clients don’t know the entire problem or don’t spend time thinking about the entire problem when they create requirements or requests for changes. Therefore, I like short cycle times, with the flexibility to change directions as necessary.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | 1 Comment

Doing a Little Research

Posted on December 2, 2024 by way0utwest

I’ve been very pleased with the direction of SSMS the last few years. As it’s been separated from SQL Server releases and gets updated more often, I think the changes from v17 though v20 have been improvements. There are still issues, but it’s been better. Now we finally have SSMS moving to a modern shell with the v21 preview and I’m excited to see how this changes the future of our tooling.

However, the PM for SSMS, Erin Stellato, posted a note on LinkedIn recently asking why people don’t read documentation. She also asks what you want to see in 21, so respond if you think there are holes in the SSMS docs. I think this post came about because of many responses that came from people who clearly hadn’t read some documentation.

I find far too often people struggling with different concepts, some of which I know a bit about and some I don’t. However, for those that I don’t, I can often figure out something about it quickly. A combination of Google, following links in those results, and increasingly, asking CoPilot/Some-AI  a question often helps me solve a problem by giving me direction and knowledge. I still have work to do, but it’s a start.

In fact, that’s how I solved a lot of the questions on SQL Server Central in the past. I’d read a question in the forums, I’d research a bit, set up an experiment, test something, and then post an answer. And at times, then getting told my solution didn’t work and figuring something else out or correcting my answer since I’d missed a bit of the context.

These days, I see lots of people struggling and I don’t quite understand why. It seems the art of scoping a problem, doing some research, some experiments, and coming up with a solution relatively quickly isn’t common, but more uncommon. Lots of friends who have colleagues/direct reports say there are plenty of others that don’t have these skills.

I’m curious for those of you willing to share, how do you go about doing some research when something doesn’t work? When you get asked to do something you don’t know how to do, or asked a question you can’t answer, what is your process? Pick a specific example if you can and let us know how you self-educate.

If you struggle to do this, or you post online and expect someone else to help you, I’d encourage you to think about how you can become more self-sufficient and teach yourself, using the resources you have. Your employer will appreciate it and it will help further your career opportunities.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

Posted in Editorial | Tagged | Comments Off on Doing a Little Research

Grinding Away: Iris Classon

Posted on December 2, 2024 by way0utwest

Iris Classon is a developer and Microsoft MVP. She has had a lot of success across the last decade plus. This is her second career, deciding in 2011 to leave her profession as a registered clinical dietician to learn to program. Here’s an excerpt of an interview:

“Well, the first few days sucked. I mean—yes, you might want to beep that out. Because our teachers are doing, counting, talking about binaries, and I had forgotten all the math. They’re talking about binary numbers and doing all the calculations. They’re talking about hardware as well, which I didn’t know anything about. I know now, but not then.

I was so frustrated. On the second day, I was like, “I want to go home.” I was kind of upset and stuff, so my teacher drove me home and we had a long chat, and he said, “Iris, come back. We kind of need a personality like that. You just come back. Give it the rest of the week and you’ll see.”

On the third day, when I came back—I did come back—we started programming. We did our first little console application, and I just realized I just got it. I would turn around and look at my classmates. Some of them just seemed confused and I was like, “What is the problem? I mean, this is logic.”

For the first time in my life, I understood what was written.”

Her first year of programming is documented on her site, and it’s a neat read to me. The important thing is to remember she documented this daily during that year. It’s summarized now, but she was grinding away.

What I like about this

This is a story I think shows practically how to improve yourself and get better at something new. I have remembered this story as I tried to learn to coach over the last ten years, and as I continue to work on trying to improve my skills.

This is also in tech, and it gives you a way to look at yourself and try to improve your skills to find a better position, or maybe the position you really want.

What I don’t like about this

Not everyone can take time off to improve for a year, though this is something most of you could do across 3-4 years.

I also don’t want to make it seem like anyone can do this. Everyone has certain genetic gifts, and they are drawn to certain things. Iris clearly is drawn to very logical work and has a gift for programming. Your experience, raw talent, and understanding might not be as good.

You can get better, but you walk your path and you have to remember that. Reading other stories can sometimes feel like I can’t do that. You won’t likely do the same thing, but you can improve yourself a lot of you grind away.

Posted in Blog | Tagged , | Comments Off on Grinding Away: Iris Classon