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

Asked

Viewed 13,600 times

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.

sql group-by

  • 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".

  • I don’t get it... ??? == t.valor that’s it?

  • 1

    @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 clause group by. I can’t use max(t.valor) otherwise it would take the maximum value - and not the maximum date value. Etc.

3 answers

7


The best alternative I can think of 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 that of the @Motta response but will not have the overhead to run the sub-select in the clause where for each record.

Here is an example from SQL Fiddle.

  • 1

    It would be the case to compare the 3 Execution Plans !!

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)

  • 1

    As long as there are no two lines with the same code and date (which is reasonable to assume), this is a good solution. + 1

  • @mgibsonbr de facto.

0

If that’s what I’m thinking, I managed to do it this way:

select * from minha_tabela
where valor = (select max(valor) from minha_tabela);
  • 2

    Rafael, welcome to the site. Note that your suggestion is basically what Motta had already said.

  • No, mine is the longest date of a key to his the highest value of the table, different cases though slightly.

Browser other questions tagged sql group-by

You are not signed in. Login or sign up in order to post.