开发者

How to ignore certain similar rows when select

开发者 https://www.devze.com 2023-03-25 03:02 出处:网络
I have the following table 开发者_运维百科 Idcol1 col2 col3 1c2m 2c36 2bdu 3e69 41v8 42bt 445g As you can see, there are duplicate value in id column, 2 and 4. I only want to select rows with uniqu

I have the following table

开发者_运维百科
Id  col1 col2 col3
1    c    2     m
2    c    3     6
2    b    d     u
3    e    6     9
4    1    v     8
4    2    b     t
4    4    5     g

As you can see, there are duplicate value in id column, 2 and 4. I only want to select rows with unique id value and ignore the following rows with duplicate id value. I just want to keep the first of the rows with duplicate values

1    c    2     m
2    c    3     6
3    e    6     9
4    1    v     8

There is FK constraint, so I cannot delete rows with duplicate values.

I am using SQL SERVER 2008 R2

Any reply will be appreciated.


You can use row_number to number each row with the same id. Then you can select only the first row per id:

select  *
from    (
        select  row_number() over (partition by id order by col1, col2, col3) rn
        from    YourTable
        ) as SubQueryAlias
where   rn = 1

The subquery is required because SQL Server doesn't allow row_number directly in the where clause.

0

精彩评论

暂无评论...
验证码 换一张
取 消