select aggregate function and all other columns

Question

How do I select all columns in a table and an aggregate function in a convenient way?

I.e. say that I have a table with 100 columns, and I want to send the following

SELECT Max(Columns 44), ALL OTHER COLUMNS
FROM zz
Group by ALL OTHER COLUMNS

Thanks!

There's no shortcut! (BTW, are you using MySQL or Teradata? Don't tag products not involved.) — jarlh, Sep 21 '16 at 09:03
No need of group by if you want to select all column's value with aggregate function. Now, you just have to add in the select only :) — Shushil Bohara, Sep 21 '16 at 09:21

Thorsten Kettner · Accepted Answer · 2016-09-21T09:27:27.060

To select all columns from the table is:

select * from zz;

To select a maximum from the table is

select max(column44) from zz;

The two combined:

select zz.*, (select max(column44) from zz) as maxcol44
from zz;

If you want to omit column44 in your result rows and only have maxcol44, then you must list the columns:

select 
  column1, 
  column2, 
  ...
  column43, 
  (select max(column44) from zz) as maxcol44,
  column45, 
  ...
from zz;

1 Answers1