Doubt in an SQL DISTINCT

Question

I have the following select:

SELECT DISTINCT CD_CLIENTE, CD_CONTRATO, CD_AGRUPADOR  FROM TBP_IMPORTAR_OS_RESIDUO WHERE CD_USUARIO = 0

But I wish when the field CD_AGRUPADOR were the same, bring only 1 record. I can not take the distinct because in some cases I will get results with different grouper values. For example I could have another record with values 83 - 327 - 0.

Answer 1

The DISTINCT function makes you bring different data, grouping the same information into as few lines as possible. If you have different data in the CD_CONTRATO field, it is not possible to bring all information in the same line.

Answer 2

Good morning, you can use the row_number passing only the cd_agrupador, if you want to add more fields. Follow the example:

SELECT *
FROM(
    SELECT  CD_CLIENTE, 
            CD_CONTRATO, 
            CD_AGRUPADOR,
            ROW_NUMBER() OVER(PARTITION BY CD_AGRUPADOR ORDER BY CD_AGRUPADOR) AS RN
    FROM 
        TBP_IMPORTAR_OS_RESIDUO  
    WHERE 
        CD_USUARIO = 0
) AS K
WHERE RN = 1