Custom Aggregate Function (to use with GROUP BY)

Question:

I'm wondering if it's possible to create a custom SQL group by function.

For example, I often need to find the most common value within a group. While I can achieve this using CTEs or subqueries, having a custom group by function like the example below would be much cleaner:

SELECT company_name, most_common(first_name) AS most_common_first_name
FROM company
GROUP BY company_name

This feels like something that should be possible, however, I haven't found many promising results online. If this is something that could be done, does anyone have any site refers or examples?

Answer:

In SQL Server, there isn't a built-in most_common() aggregate function, but you can achieve the desired result with a combination of window functions or common table expressions (CTEs). The approach you're looking for can be emulated using ROW_NUMBER() or RANK() to find the most frequent value within each group.

Here's how you can do this in dbForge Studio for SQL Server:

The RankedNames CTE counts occurrences of each first_name within each company_name group, assigns a ranking using ROW_NUMBER() based on the count in descending order, and the outer query filters for the most frequent name by selecting rows where rn = 1, though ties can be handled with RANK() or DENSE_RANK() if needed, and while there's no direct most_common() function, this approach is the closest alternative without custom CLR functions.