List values with MaxDate

Im trying to create ie query to show itens with MAX DATE, but I don´t know how ! Follow the script and result:

Select
    results.severity As "Count_severity",
    tasks.name As task,
    results.host,
    to_timestamp(results.date)::date
From
    tasks Inner Join
    results On results.task = tasks.id
Where
    tasks.name Like '%CORP 0%' And
    results.severity >= 7 And
    results.qod > 70 

I need to show only tasks with the last date of each one. Can you help me ?

Answer

You seem to be using Postgres (as suggested by the use of casting operator ::). If so – and I follow you correctly – you can use distinct on:

select distinct on(t.name) 
    r.severity, t.name as task, r.host, to_timestamp(r.date::bigint)::date
from tasks t
inner join results r on r.task = t.id
where t.name like '%corp 0%' and r.severity >= 7 and r.qod > 70 
order by t.name, to_timestamp(r.date::bigint)::date desc

This guarantees one row per task only; which row is picked is controlled by the order by clause, so the above gets the row with the greatest date (time portion left apart). If there are ties, it is undefined which row is returned. You might want to adapt the order by clause to your exact requirement, if it is different than what I understood.

---

On the other hand, if you want top ties, then use window functions:

select *
from (
    select r.severity, t.name as task, r.host, to_timestamp(r.date::bigint)::date,
        rank() over(partition by t.name order by to_timestamp(r.date::bigint)::date desc) rn
    from tasks t
    inner join results r on r.task = t.id
    where t.name like '%corp 0%' and r.severity >= 7 and r.qod > 70 
) t
where rn = 1