Picking up the last value of each ID according to the highest final year

Question

Picking up the last value of each ID according to the highest final year

Navigation

#1 by (1 votes)

0

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 return only one record of each user taking into account the highest% of% and, if final year is equal to% with%, consider the highest ano_final ?

mysql query

asked by anonymous 03.10.2017 / 15:55

1 answer

Check if you have reached the last row of the table I need an item from my select to be selected, Angular

score 1 · Accepted Answer

Whereas ano_inicial is stored as an integer:

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

Considering that 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