sql - Trying to select multiple columns where one is unique

Question

I am trying to select several columns from a table where one of the columns is unique. The select statement looks something like this:

select a, distinct b, c, d  
from mytable

The table looks something like this:

| a | b | c | d | e |...  
|---|---|---|---|---|   
| 1 | 2 | 3 | 4 | 5  
| 1 | 2 | 3 | 4 | 6  
| 2 | 5 | 7 | 1 | 9  
| 7 | 3 | 8 | 6 | 4  
| 7 | 3 | 8 | 6 | 7  

So the query should return something like this:

| a | b | c | d |  
|---|---|---|---|  
| 1 | 2 | 3 | 4  
| 2 | 5 | 7 | 1  
| 7 | 3 | 8 | 6  

I just want to remove all of the rows where b is duplicated.

EDIT: There seems to be some confusion about which row I want to be selected in the case of duplicate b values. I don't care because the a, c, and d should (but are not guaranteed to) be the same.

Answer 1

Try this

    SELECT * FROM (SELECT ROW_NUMBER() OVER (PARTITION BY b ORDER BY a) NO
,* FROM TableName) AS T1 WHERE NO = 1