D.Loo D.Loo - 2 months ago 18
MySQL Question

select aggregate function and all other columns

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!

Answer

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;
Comments