How to eliminate duplicate lines without using distinct?

Asked

Viewed 271 times

2

The query below returns me the following:

inserir a descrição da imagem aqui

       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 ? inserir a descrição da imagem aqui

I need the query to return the lines to me with DataAtualizacao most recent and eliminate the oldest, and stay that way:

inserir a descrição da imagem aqui

sql sql-server

  • Just to confirm my understanding. You want to return the results grouped by IdColuna1 who own the largest DataAtualizacao correct?

  • Grouped by Idcoluna1 and Idcoluna2.

2 answers

6


Try the following:

SELECT t1.*
  FROM #tmpHistorico t1
 WHERE t1.DataAtualizacao = (SELECT MAX(t2.DataAtualizacao)
                               FROM #tmpHistorico t2
                              WHERE t2.IdColuna1 = t1.IdColuna1
                                AND t2.IdColuna2 = t1.IdColuna2);

To make this solution more performative you can index the columns DataAtualizacao, IdColuna1 and IdColuna2.

1

I suggest you create a new temporary table to control what will be displayed or not, as this cannot be done by Id. 

IF object_id('tempdb..#tmpHistorico') IS NOT NULL BEGIN DROP TABLE #tmpHistorico END
IF object_id('tempdb..#tmpHistoricoMaxData') IS NOT NULL BEGIN DROP TABLE #tmpHistoricoMaxData 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')

CREATE TABLE #tmpHistoricoMaxData
(
    Id              int not null,
    IdColuna1       int not null,
    IdColuna2       int not null,
    DataAtualizacao DateTime not null
)

insert into #tmpHistoricoMaxData
select Id, IdColuna1, IdColuna2, max(DataAtualizacao) 
from #tmpHistorico
group by Id, IdColuna1, IdColuna2

select tmp.Id, tmp.IdColuna1, tmp.IdColuna2, tmp.Valor1, tmp.Valor2, tmp.DataAtualizacao 
from #tmpHistorico tmp
join #tmpHistoricoMaxData tmpDt on tmp.Id = tmpDt.Id and tmp.IdColuna1 = tmpDt.IdColuna1 and tmp.IdColuna2 = tmpDt.IdColuna2 and tmp.DataAtualizacao = tmpDt.DataAtualizacao
where tmp.Id= 1

Browser other questions tagged sql sql-server

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