Select a single record from those that have multiple duplicate fields

Navigation
0

Good, I have the following structure in a database 

 COD_ENTIDAD     COD_IDCONTRA          COD_CONCEGES   COD_PRODGEST
--------------------------------------------------------------
1   0182    00000000000000000000000003     403Q        00126057 
2   0182    00000000000000000000000004     101AW       012600000
3   0182    00000000000000000000000004     403Q        012600000
4   0182    00000000000000000000000004     403Q        00126057 
5   0182    00000000000000000000000004     322C        012600000
6   0182    00000000000000000000000005     322C        012600000
7   0182    00000000000000000000000006     101AW       012600000

I want to select all records, but those with the same fields COD_ENTIDAD, COD_IDCONTRA and COD_CONCEGES , select only one. For example, in the table above, I would only have the records 1, 2, 3, (the jump 4 because the 3 mentioned fields are equal to 3), 5, 6 and 7

I do not know how to put the truth, I can not think what clause I could use or what to do. Maybe with TOP or subqueries but I do not know how to raise it. I tried a join but obviously I stay the same as I was, because I keep all the records. 

SELECT A.*, B.COD_PRODGEST FROM 
(SELECT DISTINCT COD_ENTIDAD, COD_IDCONTRA, COD_CONCEGES
FROM BBDD) A
INNER JOIN
(SELECT COD_ENTIDAD, COD_IDCONTRA, COD_CONCEGES, COD_PRODGEST
FROM BBDD) B
ON A.COD_ENTIDAD= B.COD_ENTIDAD
AND A.COD_IDCONTRA = B.COD_IDCONTRA
AND A.COD_CONCEGES = B.COD_CONCEGES

Any ideas or clues about how you could do it?

    
asked by DevCodeG 19.01.2017 в 17:45
source

3 answers

2

If it does not matter which duplicate record you select, then you can try ROW_NUMBER : 

SELECT *
FROM (  SELECT  A.*, 
                B.COD_PRODGEST, 
                ROW_NUMBER() OVER(PARTITION BY A.COD_ENTIDAD, A.COD_IDCONTRA, A.COD_CONCEGES
                                  ORDER BY COD_ENTIDAD) AS RN
        FROM (  SELECT DISTINCT COD_ENTIDAD, COD_IDCONTRA, COD_CONCEGES
                FROM BBDD) A
        INNER JOIN (SELECT COD_ENTIDAD, COD_IDCONTRA, COD_CONCEGES, COD_PRODGEST
                    FROM BBDD) B
            ON A.COD_ENTIDAD= B.COD_ENTIDAD
            AND A.COD_IDCONTRA = B.COD_IDCONTRA
            AND A.COD_CONCEGES = B.COD_CONCEGES) X
WHERE RN = 1
    
answered by 19.01.2017 / 18:09
source
0

If what you need to return is only the fields COD_ENTIDAD, COD_IDCONTRA and COD_CONCEGES of a table, the query would be such that this way 

SELECT DISTINCT (COD_ENTIDAD, COD_IDCONTRA, COD_CONCEGES) FROM Nombre_de_tabla

I hope it helps.

    
answered by 19.01.2017 в 18:13
-1

You can do it with the DISTINCT command and it would be something like this: 

SELECT DISTINCT * FROM myTable WHERE 1 = 1

In your case it would be something like 

SELECT DISTINCT A.*, B.COD_PRODGEST FROM 
( /* Tu subquery aquí */)
WHERE 1= 1

Test and comment if it worked.

Greetings

    
answered by 19.01.2017 в 17:51
Problems when deploying a web application custom views