SQL Server How to select multiple columns where one column has duplicate values, without grouping

xxb16uws 于 2023-02-28 发布在其他

关注(0)|答案(2)|浏览(168)

I need to return all data for rows where one particular field in the row is the same for each record, but I can't use group by because I need to see all the duplicated records; I don't just want a count of them.

I've tried using group by and having count(field) > 1 and various forms of simple select and other ways of trying to use count() with no luck.

There are additional where requirements, but for simplicity I'm not including them here. I'm using SSMS 2008 R2.

Example:

Data:

Col1  |  Col2  |  Col3
----------------------
abc      wow      13/05/2016
abc      wow      10/05/2016
def      wow      13/05/2016
ghi      wow      13/05/2016
ghi      wow      10/05/2016
jkl      wow      01/01/2016

Expected result:

Col1  |  Col2  |  Col3
----------------------
abc      wow      13/05/2016
abc      wow      10/05/2016
ghi      wow      13/05/2016
ghi      wow      10/05/2016

I am looking to show anything that does not have a unique value in Col1

sql-server

来源：https://stackoverflow.com/questions/37212861/how-to-select-multiple-columns-where-one-column-has-duplicate-values-without-gr

2条答案

按热度按时间

b1zrtrql1#

You can use the following query:

SELECT Col1, Col2, Col3
FROM (
  SELECT Col1, Col2, Col3,
         COUNT(*) OVER (PARTITION BY Col1) AS cnt
  FROM mytable) AS t
WHERE t.cnt > 1

The query uses window version of COUNT aggregate function: the function is applied over Col1 partitions. The outer query filters out records which have a Col1 value that appears only once.

赞(0）回复(0）举报 2023-02-28

hwazgwia2#

It isn't very clear what you are trying to do but maybe something like this is what you are looking for?

with MyDupes as
(
    Select MyColumnWithDupes
        , COUNT(*) as RowCount
    from SomeTable
    Group by MyColumnWithDupes
)

select *
from SomeTable st
join MyDupes md on md.MyColumnWithDupes = st.MyColumnWithDupes
where md.RowCount > 1

赞(0）回复(0）举报 2023-02-28

我来回答

SQL Server How to select multiple columns where one column has duplicate values, without grouping

2条答案

相关问题

热门标签

最新问答