Can you MAX(*) in SQL Server?

Question

I am dealing with a huge list of columns (around 50) where i only need to group by one column. Is there anyway in SQL Server i can aggregate the columns by something such as

SELECT MAX(*)
FROM View1
GROUP BY Column1

instead of having to go through each one and specify an aggregate function. I have had a look online but cant find anything. Is there any advice or guidance someone can give me or is it just a case of going through each row?

Thanks

Answer 1

You can build query you need using system tables:

DECLARE @ViewName sysname = N'View1',
        @query nvarchar(max),
        @Column sysname = 'Column1'

SET @query = N'SELECT ' + @Column + ',' + CHAR(10)

SELECT @query = @query + N'MAX('+c.[name]+') as '+c.[name]+',' + CHAR(10)
FROM sys.views v
INNER JOIN sys.columns c
    ON v.[object_id] = c.[object_id]
WHERE v.[name] = @ViewName AND c.[name] != @Column


SET @query = STUFF(@query,LEN(@query)-1,1,'') + 'FROM '+@ViewName + CHAR(10) + 'GROUP BY ' + @Column

PRINT @query

Output will be:

SELECT Column1,
MAX(Column2) as Column2,
MAX(Column3) as Column3,
...
MAX(ColumnN) as ColumnN
FROM View1
GROUP BY Column1

You can Ctrl+C Ctrl+V on new query window and execute, or execute it right here with:

EXEC (@query)

In case of tables - you need to use sys.tables

In case if view or table is not in default schema - you need to specify it manually.

SET @query = STUFF(@query,LEN(@query)-1,1,'') + 'FROM dbo.'+@ViewName + CHAR(10) + 'GROUP BY ' + @Column

Answer 2

No. Unfortunately, you're going to have to write out the columns.

Answer 3

If you need max value from different columns you can try as below

Select max(yourcolumn) from
(
    Select col1 from yourtable
    union all Select col2 from yourtable
    union all Select col3 from yourtable
    ...
) a

Answer 4

No, you can not use max(*). You will have to give a column name in max function like below

select max(column_name) from table_name;

Answer 5

You cannot write select max(*). This results in the error Incorrect syntax near '*'. Instead, you will need to specify the columns.

One way to get the maximum of multiple columns is to unpivot the table. An efficient way to do this is to use cross apply to generate separate row values for each column.

For example, the code below finds the maximum value across 3 different columns in all of the rows:

declare @test table
(
    id int primary key clustered,
    value1 int,
    value2 int,
    value3 int
)

insert into @test (id, value1, value2, value3)
values (1, 100, 0, 0), (2, 0, 50, 0), (3, 0, 0, 25)

select max(TestValue) -- returns 100
from @test
cross apply
(
    values(value1),(value2), (value3)
) TestValues (TestValue)