CODEWITHSUNDEEP
sql-serveraggregate-functions
Brondahl
Brondahl

Reputation: 8597

"SINGLE" aggregation function in SQL Server

Moderately frequently, I find myself doing a grouping, that I know will result in the whole group having the same value in a particular column, but SQL Server doesn't know that.

Most often, it's that I've grouped by DATEPART(Month, my_date_column) and then I want to SELECT DATEPART(Year, my_date_column) where all the data is in a single year or SELECT DATENAME(Month, my_date_column)

SQL Server doesn't know that these are implicitly all identical, so I end up using MIN() or MAX().

This works, but it feels wrong. (And misleading for future developers!)

Is there a SINGLE() function or anything comparable?

Ideally it would error if they weren't all unique, but I'd taking anything that was more explicit about what I was doing.

Upvotes: 1

Views: 340

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

I just use MIN. There are only 13 aggregate functions and there's nothing that is more suitable.

If you wish to document that the result should be unique for the group and that multiple values are an error, put a tripwire in:

...
MIN(Expression) as a,
CASE WHEN MIN(Expression) != MAX(Expression) THEN 1/0 END as EnsureUnique,
...

The alternative is to write your own CLR Aggregate function for this.

Upvotes: 1

Related Questions