SQL Server能否使用MAX(*)？多列聚合无需逐个指定函数的方法问询

Great questions! Let's tackle these two SQL Server scenarios one by one.

Can you use MAX(*) in SQL Server? #

Short answer: No, MAX(*) isn't valid syntax here. The MAX() aggregate function requires a single, scalar expression (like a specific column name, a calculated value, or a literal) as its input. Using * doesn't work because it represents all columns in the table—there's no logical way to evaluate "maximum" across a mix of different data types and columns.

If you were trying to grab the "top" row from each group (like the most recent entry or the row with the highest ID), window functions are a better fit. For example, to get the latest row per group based on a date column:

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY YourGroupColumn ORDER BY YourDateColumn DESC) AS RowRank
    FROM YourTableName
) RankedRows
WHERE RowRank = 1;

This gives you the full row for each group, which is probably what you were aiming for when thinking about MAX(*).

Aggregating 50+ Columns Without Specifying Each One Manually #

SQL Server doesn't have a built-in shortcut like AGGREGATE(*) to apply an aggregate function to all non-grouping columns automatically. But there are practical workarounds to avoid typing every single column name:

1. Use Dynamic SQL to Generate the Query #

This is the most scalable approach for large tables. You can query SQL Server's system catalog views to automatically build your aggregation query.

Here's a ready-to-use example that generates a query using MAX() for all columns except your grouping column (replace YourTableName and YourGroupColumn with your actual table and grouping column):

DECLARE @GroupCol NVARCHAR(128) = 'YourGroupColumn';
DECLARE @TableName NVARCHAR(128) = 'YourTableName';
DECLARE @Sql NVARCHAR(MAX);

SELECT @Sql = 'SELECT ' + @GroupCol + ', ' + STRING_AGG(
    QUOTENAME(c.name) + ' = MAX(' + QUOTENAME(c.name) + ')',
    ', '
) + ' FROM ' + QUOTENAME(@TableName) + ' GROUP BY ' + @GroupCol
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = @TableName
AND c.name != @GroupCol;

EXEC sp_executesql @Sql;

• Swap out MAX() with the right aggregate function (like MIN(), SUM(), or AVG()) depending on what you need for each column.
• If you're on SQL Server 2016 or older, STRING_AGG() isn't available—use FOR XML PATH to concatenate the column list instead.

2. Verify if You Actually Need Aggregation #

Sometimes aggregation is overkill. If each group in your grouping column has only one unique set of values across the other columns, you can simplify things:

• Use GROUP BY YourGroupColumn, AllOtherColumns (though you still need to list the columns, but SSMS can generate this for you)
• Or use SELECT DISTINCT YourGroupColumn, * FROM YourTableName (though DISTINCT * is redundant if your grouping column is the only key)

Alternatively, if you want a specific row per group (not aggregated values), window functions like ROW_NUMBER() (as shown earlier) let you pull full rows without aggregating every column.

3. Use SSMS's Built-in Script Generator #

If you prefer a GUI approach:

• Right-click your table in SQL Server Management Studio (SSMS)
• Hover over Script Table as > SELECT To > New Query Editor Window
• SSMS will auto-generate a query with all your column names. You just need to wrap each non-grouping column in your chosen aggregate function and add the GROUP BY clause—way faster than typing 50 columns by hand.

内容的提问来源于stack exchange，提问作者Ryan Gadsdon