CODEWITHSUNDEEP
sqlsql-server
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Can you MAX(*) in SQL Server?

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

Upvotes: 1

Views: 1048

Answers (5)

Paul Williams
Paul Williams

Reputation: 17040

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)

Upvotes: 0

gofr1
gofr1

Reputation: 15987

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

Upvotes: 5

Vivek Kumar
Vivek Kumar

Reputation: 17

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;

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

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

Upvotes: 0

Joshua Schlichting
Joshua Schlichting

Reputation: 3450

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

Upvotes: 1

Related Questions