Select rows with some records having duplicated value in one column-CodePudding

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:

I can select any row from the set of records with the same stringIdentifier
Only column id is PRIMARY KEY
It could be rows with text = NULL and stringIdentifier = NULL

Thanks in advance.

CodePudding user response：

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

CodePudding user response：

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);