How to select a specific row based on another row from same table 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   |
+--------+------+---------------+
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