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

That is not valid syntax in any SQL product I know, including SQL Server. — Larry Lustig, Feb 08 '18 at 15:54
So what would the expected output of that be? The maximum value of the biggest column? The maximum value of the sum of all columns? What if there are non-numeric columns in the table? Maybe you should take a step back and explain the underlying problem by showing some [sample data](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. ([Formatted text](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557)). — , Feb 08 '18 at 15:56
See [SQL MAX of multiple columns?](https://stackoverflow.com/questions/71022/sql-max-of-multiple-columns) — Alex K., Feb 08 '18 at 16:01
Simple select top 1000 rows by right clicking table . u will have all column names displayed in query window, select the aggregate column u wanted . a simple shortcut — Ven, Feb 08 '18 at 16:09
It is a common situation to me. F.e. you are building facts table, and it consist of 50 columns. And you need to use aggregation on half of this columns. Ctrl+c ctrl+v 25 times, then put ') as columnname'.. it takes time :) — gofr1, Feb 08 '18 at 16:36
@gofr1 there are better ways to do this kind of stuff. Notepad++ supports vertical block editing, plus a good regexp replace will go a long way for your use-case — trailmax, Feb 08 '18 at 16:38

gofr1 · Accepted Answer · 2018-02-08T16:15:00.823

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

score 1 · Answer 2 · answered Feb 08 '18 at 15:55

1

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

answered Feb 08 '18 at 15:55

Joshua Schlichting

3,110
6
28
54

score 0 · Answer 3 · answered Feb 08 '18 at 16:00

0

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

answered Feb 08 '18 at 16:00

Kannan Kandasamy

13,405
3
25
38

score 0 · Answer 4 · answered Feb 08 '18 at 16:09

0

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;

answered Feb 08 '18 at 16:09

Vivek Kumar

17
7

score 0 · Answer 5 · answered Feb 08 '18 at 16:24

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)

Can you MAX(*) in SQL Server?

5 Answers5