8
I have a table with columns codigo
, data
and valor
. I would like to get, for each code, the value corresponding to the most recent date. In what ways can this be done? And if there is more than one, which is the most efficient. Example:
minha_tabela
codigo data valor
-------------------------------
1 2014-01-01 10
1 2014-01-02 20
2 2014-01-03 30
2 2014-01-04 40
2 2014-01-05 50
3 2014-01-06 60
Expected result:
codigo data valor
-------------------------------
1 2014-01-02 20
2 2014-01-05 50
3 2014-01-06 60
Get the latest date is easy: just make one group by
aggregating max
. The problem is getting the value corresponding to that date:
select t.codigo as codigo, max(t.data) as data, ??? as valor
from minha_tabela t
group by t.codigo;
I would like a solution that works in "standard" SQL, without depending on particular features of any specific DBMS.
Note: this is a common and recurring question of mine, and I miss an easy-to-find "canonical" question. There are other similar questions (example, example) but that are kind of "hidden" in other aspects, so I wanted to do one that goes "straight to the point".
– mgibsonbr
I don’t get it...
??? == t.valor
that’s it?– Jorge B.
@Jorgeb. That was just an indication that I don’t know what to do to find the right value. I can’t use
t.valor
because he is not present in the clausegroup by
. I can’t usemax(t.valor)
otherwise it would take the maximum value - and not the maximum date value. Etc.– mgibsonbr