2
The query below returns me the following:
IF object_id('tempdb..#tmpHistorico') IS NOT NULL
BEGIN
DROP TABLE #tmpHistorico
END
CREATE TABLE #tmpHistorico
(
Id int not null,
IdColuna1 int not null,
IdColuna2 int not null,
Valor1 varchar(10) not null,
Valor2 varchar(10) not null,
DataAtualizacao DateTime not null
)
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (1,1,1,'100', '2000','2018-04-09 00:00:00.000')
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (1,2,2,'100', '99999','2018-04-09 00:00:00.000')
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (1,2,2,'100', '200000','2018-04-10 00:00:00.000')
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (1,3,3,'100', '259999','2018-04-09 00:00:00.000')
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (1,4,4,'100', '109999','2018-04-09 00:00:00.000')
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (1,4,4,'100', '9999','2018-04-10 00:00:00.000')
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (1,5,5,'100', '2559999','2018-04-09 00:00:00.000')
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (2,6,6,'100', '159999','2018-04-09 00:00:00.000')
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (2,6,6,'100', '309999','2018-04-10 00:00:00.000')
insert #tmpHistorico (Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao) values (2,7,7,'100', '559999','2018-04-09 00:00:00.000')
select Id, IdColuna1, IdColuna2, Valor1, Valor2, DataAtualizacao
from #tmpHistorico where Id= 1
DOUBT
How do I delete duplicate lines ?
I need the query to return the lines to me with DataAtualizacao
most recent and eliminate the oldest, and stay that way:
Just to confirm my understanding. You want to return the results grouped by
IdColuna1
who own the largestDataAtualizacao
correct?– Anthony Accioly
Grouped by Idcoluna1 and Idcoluna2.
– hard123