Trying to select multiple columns where one is uni

问题:

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.

回答1:

Try this

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

回答2:

I think you are nearly there with DISTINCT try:

SELECT DISTINCT a, b, c, d
FROM myTable

回答3:

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

回答4:

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

回答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

回答6:

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.