i want to remove rows has duplicate value in specific column

my Query return data like this:

I want it like this:

MY QUERY VALUE DUPLICATE IS t2.numeroLot

select t2.nomClient,t2.numeroTlf,t2.numeroLot,t2.nature,t2.prixPaiement,t1.paym,t1.payer from ((select dossier.refDossier, (Paiement.prixPaiement SUM(CONVERT(float,PaiementDeclare.montant))) as paym,SUM(CONVERT(float,PaiementDeclare.montant)) as payer from payment 
left join dossier on dossier.idPaiement = payment.idPaiement left join PaiementDeclare on PaiementDeclare.idPaiement = Paiement.idPaiement group by  Paiement.prixPaiement,dossier.refDossier) 
left join (select dossier.refDossier,client.nomClient,client.numeroTlf,lot.numeroLot,lot.nature, Paiement.prixPaiement from dossier 
left Join dossierClient on dossierClient.idDossier = dossier.idDossier 
left join client on client.idClient = dossierClient.idClient 
left join dossierLot on dossierLot.idDossier = dossier.idDossier 
left join lot on lot.idLot = dossierLot.idLot   
left join Paiement on Paiement.idPaiement = dossier.idPaiement  
left join PaiementDeclare on PaiementDeclare.idPaiement = Paiement.idPaiement
group by prixPaiement,dossier.refDossier,client.nomClient,numeroTlf,lot.numeroLot,lot.nature) t2
on t2.refDossier = t1.refDossier)  
where t1.refDossier is not null```

CodePudding user response：

You can use ROW_NUMBER() to identify the rows you want (the first one of each group). Then filtering out is easy.

Your query could take the form:

select *
from (
  select
    row_number() over(partition by t2.numeroLot order by t2.nomClient) as rn,
    -- rest of your big query here...
) x
where rn = 1