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 most recent date for each code, however, there will be repeated dates, and when this occurs I will have to pick up the largest 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
See the example below for how you can do it.
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
You just need to check the records that do not have data or valor greater than 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)
One more guess ...
For the initial date I have several records (read sequences 1 to 10). But I want to select the longest date and the longest date.
Code for this solution (may be useful to 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);
It seems to me that the simplest solution to the problem is this:
select codigo,MAX(valor)
from minha_tabela
group by codigo