Get last record of each id in sql query with condition

Question

Get last record of each id in sql query with condition

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

I'm having trouble getting the last record of every id in a 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 is returning the following data in that order, I would like you to show me the most current record of each code.

sql sql-server

asked by anonymous 26.05.2015 / 21:29

2 answers

0

try SELECT IDENT_CURRENT(‘YourTable’) See more here

26.05.2015 / 21:41

HTML editor made in HTML Eclipse or android studio?

score 3 · Accepted Answer

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