Select max value in a year for every phrase-CodePudding

semantics=# \d frequencies;
                              Table "public.frequencies"
  Column   |  Type   | Collation | Nullable |                 Default                 
----------- --------- ----------- ---------- -----------------------------------------
 id        | integer |           | not null | nextval('frequencies_id_seq'::regclass)
 april     | integer |           | not null | 0
 august    | integer |           | not null | 0
 december  | integer |           | not null | 0
 february  | integer |           | not null | 0
 january   | integer |           | not null | 0
 july      | integer |           | not null | 0
 june      | integer |           | not null | 0
 march     | integer |           | not null | 0
 may       | integer |           | not null | 0
 november  | integer |           | not null | 0
 october   | integer |           | not null | 0
 september | integer |           | not null | 0
 phrase_id | integer |           | not null | 
Indexes:
    "frequencies_pkey" PRIMARY KEY, btree (id)
    "unique_phrase_for_frequency" UNIQUE CONSTRAINT, btree (phrase_id)
Foreign-key constraints:
    "phrase_fk" FOREIGN KEY (phrase_id) REFERENCES phrases(id)

Could you help me write a select that will do (in pseudocode)

select phrase_id, max(january, february, ..., december);

CodePudding user response：

Check the function GREATEST this is exact what you are looking for

The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions.

In PostgreSQL you may use it (contrary to e.g. Oracle implementation) even in case that some month contains a NULL value.

NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.