How to get the value of a column corresponding to the maximum of another column?

Navigation
7

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

Getting the latest date is easy: just group by with max aggregation. The problem is getting the value for 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 the "standard" SQL, without depending on the particular characteristics of any specific DBMS.

    
asked by anonymous 03.08.2014 / 19:57

3 answers

7

The best alternative that comes to mind is 

select m.*
  from minha_tabela m
 inner join (
       select codigo, max(data) data
         from minha_tabela
        group by codigo) x
 using(codigo, data);

which is the same as 

select m.*
  from minha_tabela m
 inner join (
       select codigo, max(data) data
         from minha_tabela
        group by codigo) x
    on m.codigo=x.codigo and m.data=x.data;

The result will be the same as the @Motta response but will not have the overhead of executing the sub-select in the where clause for each record.

Follow the example in SQL Fiddle .

    
03.08.2014 / 21:46
6
select  *
from tabela t1
where data = (select max(t2.data)
              from tabela t2
              where t2.codigo = t1.codigo)

A solution using subselect (there are others)

    
03.08.2014 / 20:45
0

If that's what I'm thinking, I was able to do the following: 

select * from minha_tabela
where valor = (select max(valor) from minha_tabela);
    
11.11.2015 / 21:02
Simple comparison between dates How to intercept exceptions when working with the Entity Framework?