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.

Vorkos · Answer 1 · 2022-10-10T12:39:47.213

0

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

edited Oct 10 '22 at 12:39

answered Oct 10 '22 at 11:44

Vorkos

212
1
11

Sorry, i forgot to clarify one case. Your answer works perfectly, but it could exists a case where column "text" could be also null. I will edit the post. Thank you very much. – xaenea Oct 10 '22 at 11:59
You've not specified how you would like to handle the null-case. Do you want to include all rows with nulls? – Vorkos Oct 10 '22 at 12:32

score 0 · Accepted Answer · answered Oct 10 '22 at 12:47

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

Select rows with some records having duplicated value in one column

2 Answers2