Select rows with some records having duplicated value in one column

Question

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.

Answer 1

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