CODEWITHSUNDEEP
sqlselectduplicatesrow
xaenea
xaenea

Reputation: 13

Select rows with some records having duplicated value in one column

I would like to retrieve all rows from a table where there are some records with a duplicated column, but i have to select only one row for those cases.

Example: 
 -------------------------------------------
| id   |   text          | stringIdentifier |
|-------------------------------------------
| 1    |  exampleTEXT1   | NULL             |  
| 2    |  exampleTEXT2   | NULL             | 
| 3    |  exampleTEXT3   | X13UIWF          |
| 4    |  exampleTEXT3   | X13UIWF          |
| 5    |  exampleTEXT3   | X13UIWF          |
| 6    |  exampleTEXT4   | A78BCTK          |
| 7    |  exampleTEXT4   | A78BCTK          |
| 8    |  NULL           | NULL             |
| 9    |  NULL           | NULL             |
 -------------------------------------------

Expected output: 
 -------------------------------------------
| id   |   text          | stringIdentifier |
|-------------------------------------------
| 1    |  exampleTEXT1   | NULL             |  
| 2    |  exampleTEXT2   | NULL             | 
| 3    |  exampleTEXT3   | X13UIWF          |
| 6    |  exampleTEXT4   | A78BCTK          |
| 8    |  NULL           | NULL             |
| 9    |  NULL           | NULL             |
 -------------------------------------------

Notes:

Thanks in advance.

Upvotes: 1

Views: 91

Answers (2)

DannySlor
DannySlor

Reputation: 4620

We can use rank() to choose only the first time ordered by id where any text appears.

select  id
       ,text
       ,stringidentifier
from   (
        select  *
                ,rank() over(partition by text order by id) as rnk
        from    t 
       ) t
where  rnk = 1 
or     text is null
id text stringidentifier
1 exampleTEXT1 null
2 exampleTEXT2 null
3 exampleTEXT3 X13UIWF
6 exampleTEXT4 A78BCTK
8 null null
9 null null

Fiddle

Upvotes: 0

Vorkos
Vorkos

Reputation: 211

SELECT * FROM table
    WHERE id IN
    (
        SELECT MIN(id) FROM table
        GROUP BY text, stringIdentifier
    );

Here we are selecting rows where their ID is in the second statement (the one in parenthesis). This second statement is grouping rows by text and stringIdentifier and then from each grouping selecting the MIN(id) or minimum ID value. As there is only one minimum ID value per text/stringIdentifier pairing we end up with unique rows.

If you would like to keep ALL rows where text is NULL and stringIdentifier is NULL you can just add this to the end:

 OR (text IS NULL AND stringIdentifier IS NULL);

Upvotes: 0

Related Questions