Get last record of each id in sql query on condition

Question

I’m having trouble getting the last record of each id in an SQL Server table.

select c.Serial,v.Descricao,v.Placa,v.Cor,v.AnoFabricacao,v.Chassi
c.DataHora,c.Endereco from [CheckPoint] c inner join Equipamento e on e.Serial= c.Serial
inner join Veiculo v on v.Codigo=e.CodigoVeiculo 
where v.Codigo in(44,45)
group by v.codigo,c.Serial,v.Descricao,v.Placa,v.Cor,v.AnoFabricacao,v.Chassi
,c.DataHora,c.Endereco
order by 
c.DataHora desc

This query this returning me the following data in that order, I would like you to show me the most current record of each code.

Answer 1

I understand that when you say the last record you want the one with the most current date.

select c.Serial,
       v.Descricao,
       v.Placa,
       v.Cor,
       v.AnoFabricacao,
       v.Chassi,
       max(c.DataHora) as DataHora,
       c.Endereco 
from [CheckPoint] c 
inner join Equipamento e 
   on e.Serial= c.Serial
inner join Veiculo v 
   on v.Codigo=e.CodigoVeiculo 
where v.Codigo in(44,45)
group by v.codigo, c.Serial, v.Descricao, v.Placa, v.Cor, v.AnoFabricacao, v.Chassi, c.Endereco
order by c.DataHora desc

or

select Serial,
       Descricao,
       Placa,
       Cor,
       AnoFabricacao,
       Chassi
       DataHora as DataHora,
       Endereco
from
(
    select c.Serial,
           v.Descricao,
           v.Placa,
           v.Cor,
           v.AnoFabricacao,
           v.Chassi
           c.DataHora as DataHora,
           c.Endereco,
           row_number() over (partition by v.Codigo order by c.DataHora desc) rn
    from [CheckPoint] c 
    inner join Equipamento e 
       on e.Serial= c.Serial
    inner join Veiculo v 
       on v.Codigo=e.CodigoVeiculo 
    where v.Codigo in(44,45)
    group by v.codigo, c.Serial, v.Descricao, v.Placa, v.Cor, v.AnoFabricacao, v.Chassi, c.DataHora, c.Endereco
    ) Resultados
    where rn = 1
    order by DataHora desc

Answer 2

try SELECT IDENT_CURRENT(‘YourTable’) See more here