CODEWITHSUNDEEP
sqlpostgresql
John Smith
John Smith

Reputation: 2886

Select MAX of multiple Attributes

I have a table that contains 3000 attributes (its for a data mining experiment) The table looks like

id attr1 attr2, attr3
a  0         1       0
a  1         0       0
a  0         0       0
a  0         0       1

I wish to have it in the format

id, attr1, attr2, attr3
a     1       1        1

The values can only be 0 or 1 so, i think just getting the max of each column and grouping it by the ID would achieve this However, i don't wish to Type MAX (attr X) for each and every attribute Does anyone know a quick way of implementing this

Thank you very much for your help in advance

Upvotes: 0

Views: 1004

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This is easy enough with group by:

select id, max(attr1) as attr1, max(attr2) as attr2, max(attr3) as attr3
from t
group by id

If you don't want to do all this typing, put your list of columns in Excel. Add in a formula such as =" max("&A1&") as "&A1&",". Then copy the cell down and copy the result to where your query is.

You can also do this in SQL, with something like:

select '      max('||column_name||') as '||column_name||','
from INFORMATION_SCHEMA.columns c
where table_name = <your table name here> and column_name like 'attr%'

When you do these last two, remember to remove the final comma from the last row.

Upvotes: 3

Oleg Ignatov
Oleg Ignatov

Reputation: 865

You have to use some aggregating function in order to use attributes which are not in the group statement. So there is no any quicker way.

Upvotes: 0

Related Questions