CODEWITHSUNDEEP
sqlvb.net
Clovis Ribeiro
Clovis Ribeiro

Reputation: 45

SQL Duplicate remover

I have the folowing table:

**CDU_NomeFicheiro                  CDU_DataCriacao         CDU_Estado**
FicheiroBanco.xls                   2015-09-28 09:27:01.000   I
FicheiroBanco.xls                   2015-09-25 17:27:43.000   I
FicheiroBanco20150928v1.2.xls       2015-09-29 16:03:12.000   I
FicheiroBanco20150928teste11.xls    2015-09-28 09:45:15.000   I
FicheiroBanco20150928teste.xls      2015-09-28 09:28:36.000   I
FicheiroBanco20150928v1.2.xls       2015-09-29 16:03:11.000   E

And this is my query:

select distinct CDU_NomeFicheiro as 'Ficheiro'
, CDU_DataCriacao AS 'Data'      
from TDU_HistoricoIntegracoes 
where CDU_Estado = 'I' and CDU_NomeFicheiro is not null 
order by CDU_DataCriacao

And this is my result:

**Ficheiro                          Data**
FicheiroBanco.xls                   2015-09-25 17:27:43.000
FicheiroBanco.xls                   2015-09-28 09:27:01.000
FicheiroBanco20150928teste.xls      2015-09-28 09:28:36.000
FicheiroBanco20150928teste11.xls    2015-09-28 09:45:15.000
FicheiroBanco20150928v1.2.xls       2015-09-29 16:03:12.000

And this is the result I want:

*FicheiroBanco.xls                  2015-09-25 17:27:43.000* have to be removed
FicheiroBanco.xls                   2015-09-28 09:27:01.000
FicheiroBanco20150928teste.xls      2015-09-28 09:28:36.000
FicheiroBanco20150928teste11.xls    2015-09-28 09:45:15.000
FicheiroBanco20150928v1.2.xls       2015-09-29 16:03:12.000

It should show just the most recent row for each file, removing older duplicates with the same file name.

Upvotes: 1

Views: 53

Answers (2)

C.Fasolin
C.Fasolin

Reputation: 319

this work:

SELECT CDU_NomeFicheiro as 'Ficheiro'
        ,MAX(CDU_DataCriacao) AS 'Data'      
    FROM [32851584_HistoricoIntegracoes]
    WHERE CDU_Estado = 'I' and CDU_NomeFicheiro is not null 
    GROUP BY CDU_NomeFicheiro
    ORDER BY Data

Upvotes: 0

Greg Viers
Greg Viers

Reputation: 3523

You are using SELECT DISTINCT, but for one of the columns you want to select one value from among several, you should be using MAX() and GROUP BY instead:

SELECT 
    CDU_NomeFicheiro AS 
    'Ficheiro', 
    MAX(CDU_DataCriacao) AS 'Data'
FROM 
    TDU_HistoricoIntegracoes
WHERE CDU_Estado = 'I'
    AND CDU_NomeFicheiro IS 
    NOT NULL
GROUP BY CDU_NomeFicheiro 
ORDER BY MAX(CDU_DataCriacao)

Edit: Since I added an aggregate function for CDU_DataCriacao, I should have also added the MAX() for the ORDER BY clause as well. I have made that change now. Does it work?

Upvotes: 3

Related Questions