Trying to select multiple columns where one is uni

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

You haven't said how to pick a row for each b value, but this will pick one for each.

Select
    a,
    b,
    c,
    d,
    e
From (
    Select
        a,
        b,
        c,
        d,
        e,
        row_number() over (partition by b order by b) rn
    From
        mytable
    ) x
Where
    x.rn = 1

Answer 2

You cannot put DISTINCT on a single column. You should put it right after the SELECT:

SELECT DISTINCT a, b, c, d
FROM mytable

It return the result you need for your sample table. However if you require to remove duplicates only from a single column (which is not possible) you probably misunderstood something. Give us more descriptions and sample, and we try to guide you to the right direction.

Answer 3

I think you are nearly there with DISTINCT try:

SELECT DISTINCT a, b, c, d
FROM myTable

Answer 4

Try this

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

Answer 5

This will return what you're looking for but I think your example is flawed because you've no determinism over which value from the e column is returned.

Create Table A1 (a int, b int, c int, d int, e int)

INSERT INTO A1 (a,b,c,d,e) VALUES (1,2,3,4,5)
INSERT INTO A1 (a,b,c,d,e) VALUES (1,2,3,4,6)
INSERT INTO A1 (a,b,c,d,e) VALUES (2,5,7,1,9)
INSERT INTO A1 (a,b,c,d,e) VALUES (7,3,8,6,4)
INSERT INTO A1 (a,b,c,d,e) VALUES (7,3,8,6,7)

SELECT * FROM A1

SELECT  a,b,c,d
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY b ORDER BY a) RowNum ,* 
    FROM A1 
) As InnerQuery WHERE RowNum = 1

Answer 6

If you don't care what values you get for B, C, D, and E, as long as they're appropriate for that key, you can group by A:

 SELECT A, MIN(B), MIN(C), MIN(D), MIN(E)
 FROM MyTable
 GROUP BY A

Note that MAX() would be just as valid. Some RDBMSs support a FIRST() aggregate, or similar, for exactly these circumstances where you don't care which value you get (from a certain population).