CREATE TABLE meso (
    mes_kwdikos       serial          PRIMARY KEY,
    mes_onoma         varchar(16)     UNIQUE,
    mes_typos         varchar(16),
    CHECK (mes_typos in ('TV', 'radio', 'efimerida', 'internet'))
);

CREATE TABLE diafimisi (
    diaf_ekth_kwdik   integer         REFERENCES ekthesi(ekth_kwdikos) ON DELETE RESTRICT,
    diaf_mes_kwdik    integer         REFERENCES meso(mes_kwdikos) ON DELETE RESTRICT,
    diaf_date         date,
    diaf_kostos       money,
    PRIMARY KEY (diaf_ekth_kwdik, diaf_mes_kwdik, diaf_date)
);


create temporary view diafimisi_meso (typos, onoma, kostos) as
    select mes_typos, mes_onoma, diaf_kostos
    from diafimisi, meso
    where diaf_mes_kwdik = mes_kwdikos;

select typos, onoma, kostos
from diafimisi_meso
where (typos, kostos) in
    (select typos, max(kostos)
    from diafimisi_meso
    group by typos);

-- or

select mes_typos, mes_onoma, diaf_kostos
from diafimisi inner join meso on diaf_mes_kwdik = mes_kwdikos
where (mes_typos, diaf_kostos) in
    (select mes_typos, max(diaf_kostos)
    from diafimisi inner join meso on diaf_mes_kwdik = mes_kwdikos
    group by mes_typos);

-- or

-- 01:31 < olspookishmagus> ara mporeis na kaneis prwta sort ton pinaka pros to megista tou ka8e typou
-- 01:31 < olspookishmagus> kai meta apo to join na pareis ta onomata
-- 01:31 < olspookishmagus> ta megista*