1

I have a table with duplicated rows, I want to select only one row out of many that has a field populated in it.

Example:

  +--------+------+---------------+
  |  id    | col1 |     col2      |
  +--------+------+---------------+
  | 111111 |    9 | Not Specified |
  | 111111 |    1 | Green         |
  | 222222 |    9 | Not Specified |
  | 333333 |    1 | Blue          |
  | 444444 |    9 | Not Specified |
  | 444444 |    2 | Undisclosed   |
  +--------+------+---------------+

Output:

+--------+------+---------------+
|  id    | col1 |     col2      |
+--------+------+---------------+
| 111111 |    1 | Green         |
| 222222 |    9 | Not Specified |
| 333333 |    1 | Blue          |
| 444444 |    2 | Undisclosed   |
+--------+------+---------------+
5
  • Is there any reason to delete the first duplicated?
    – McNets
    Mar 10 '17 at 23:20
  • Yes, once I have the values for col1, col2 for the specific id I no longer need the id with the unspecified information and i would like to delete it.
    – user119472
    Mar 10 '17 at 23:25
  • I mean, does it matter if I delete first or last duplicated?
    – McNets
    Mar 10 '17 at 23:26
  • You may want to add some explanation of why the expected result looks like it does.
    – mustaccio
    Mar 10 '17 at 23:55
  • 2
    @user119472 If you're going to use an unregistered (cookie-based) account, you'll need to access the site from the same device and browser each time to retain control of your question. Otherwise, register, then ask for your accounts to be merged.
    – Paul White
    Mar 11 '17 at 0:43
1

Add a ROW_NUMBER(), and select all records where row_number = 1.

with dupli as
(
    select id, col1, col2, row_number() over (partition by id order by col1) rn
    from @dup
)
select     d1.*
from       @dup d1
inner join dupli d2
on         d1.id = d2.id
and        d1.col1 = d2.col1
and        d2.rn = 1;

|id    |col1|col2         |
|:-----|---:|:------------|
|111111|   1|Green        |
|222222|   9|Not Specified|
|333333|   1|Blue         |
|444444|   2|Undisclosed  |

dbfiddle here

1

Use row_number()

select id, col1, col2 
from ( select id, col1, col2
            , row_number() over (partition by id order by col1) rn
       from table 
     ) tt 
where rn = 1

Not the answer you're looking for? Browse other questions tagged or ask your own question.