Picking last value of each ID according to highest end year

Question

I have the following query:

select f.id_user as user, f.id_tempo, t.ano_inicial, t.ano_final 
from fato as f inner join tempo as t 
where f.id_tempo = t.id_tempo;

Which returns me the following table:

What should I use in query to returns only one record of each user taking into account the largest ano_final and, if the final year is equal to 0, take into consideration the greater ano_inicial?

Answer 1

whereas the ano_inicial is stored as whole:

select f.id_user as user, f.id_tempo, t.ano_inicial, t.ano_final 
from fato as f inner join tempo as t
where f.id_tempo = t.id_tempo 
  and t.ano_inicial = (select MAX(t1.ano_inicial) 
                       fato as f1 inner join tempo as t1
                       where f1.id_tempo = t1.id_tempo and f.id_user = f1.id_user);

edited

whereas there may be more than one occurrence of ano_inicial (returning more than one record, as commented), you limit the return:

select temp_table.id_user as user, temp_table.id_tempo, temp_table.ano_inicial, temp_table.ano_final 
from(
    select f.id_user as user, f.id_tempo, t.ano_inicial, t.ano_final 
    from fato as f inner join tempo as t
    where f.id_tempo = t.id_tempo 
      and t.ano_inicial = (select MAX(t1.ano_inicial) 
                           fato as f1 inner join tempo as t1
                           where f1.id_tempo = t1.id_tempo and f.id_user = f1.id_user);
) as temp_table
ORDER BY temp_table.id_tempo DESC 
LIMIT 1