Untitled

dblab=# select m.mes_typos, m.mes_onoma, max(d.diaf_kostos) from diafimisi as d, meso as m where d.diaf_mes_kwdik = m.mes_kwdikos group by m.mes_typos,m.mes_onoma;
 mes_typos |  mes_onoma   |      max      
-----------+--------------+---------------
 internet  | www.texnh.gr | $5,000,000.00
 efimerida | proto thema  |    $50,000.00
 tv        | ET1          |    $10,000.00
 efimerida | ta nea       |    $30,000.00
(4 rows)

dblab=# select m.mes_typos, max(d.diaf_kostos) from diafimisi as d, meso as m where d.diaf_mes_kwdik = m.mes_kwdikos group by m.mes_typos;
 mes_typos |      max      
-----------+---------------
 efimerida |    $50,000.00
 tv        |    $10,000.00
 internet  | $5,000,000.00
(3 rows)

(goal is to display the mes_onoma that has the maximum diaf_kostos from all entries of same mes_typos)


if it were one table i could do like in here:

select art_xwra, art_onoma, art_eponymo, art_im_genn
from artist 
where (art_im_genn, art_xwra) in  
    (select min(art_im_genn), art_xwra 
    from artist 
    group by art_xwra);

but it's not... maybe create a temporary view (with ... () as) which joins the 2 tables?




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