Sql - Query of two maximum values

Asked

Viewed 593 times

2

Suppose we have this database

codigo, data, valor,seq
(1, '2014-01-01', 10,2),
(1, '2014-01-02', 20,1),
(2, '2014-01-03', 30,1),
(2, '2014-01-05', 40,1),
(2, '2014-01-05', 50,2),
(3, '2014-01-06', 60,1);

I want to get the latest date by each code, however, there will be repeated dates, and when this occurs I will have to get the biggest Seq.

That is, the expected result is

1, '2014-01-02', 20,1
2, '2014-01-05', 50,2
3, '2014-01-06', 60,1

http://sqlfiddle.com/#! 18/ba9e1/1/0

sql database sql-server

  • Mysql or Sqlserver?

  • @Sveen Sqlserver

4 answers

2

You just need to check the records you don’t have data or valor greater with the NOT EXISTS:

SELECT m.*
  FROM minha_tabela m
 WHERE NOT EXISTS(SELECT 1
                    FROM minha_tabela m2
                   WHERE m2.codigo = m.codigo
                     AND (m2.data > m.data
                      OR m2.valor > m.valor)
                   GROUP BY m2.codigo,
                            m2.data,
                            m2.valor)

  • and if the date and value are equal for the same code ? heheh, something was missing for SEQ..

  • @Marconciliosouza o GROUP BY solves this problem :D In the example he left in the fiddle there is this case that you commented

  • Try changing the value to 40 on the date (2, '2014-01-05', 50.2), and see what happens :)

  • 1

    @Marconciliosouza actually think that GROUP BY has to be in the query outsider

  • @Sorack in the suggestion presented I think it is enough to change "value" by "seq", no?

1

One more assumption...

For the initial date I have several records (read sequences 1 to 10). But I want to select the largest date and from that date the largest sequence.

Code for this solution (may be useful for someone):

SELECT m.codigo, m.data,  m.valor,  m.seq 
FROM minha_tabela m
where m.data = (select max(m2.data) from minha_tabela m2
                where m.codigo = m2.codigo)
and m.seq = (select max(m3.seq) from minha_tabela m3
             where m.codigo = m3.codigo
             and   m.data = m3.data);

1


See the example below of how you can do.

SELECT m.codigo, m.data,  m.valor,  m.seq 
FROM @minha_tabela m
join 
(
  SELECT m2.codigo, max(m2.data) as data, max(m2.seq) as seq
  FROM @minha_tabela m2
  group by m2.codigo
) m3 on m3.codigo = m.codigo
and m3.data = m.data
and m3.seq = m.seq

-1

It seems to me that the simplest solution to the problem is this:

select codigo,MAX(valor)
from minha_tabela
group by codigo

Browser other questions tagged sql database sql-server

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