How can I find groups with more than one rows and list the rows in each such group?-CodePudding

I have a table "mytable" in a database. Given a subset of the columns of the table, I would like to group by the subset of the columns, and find those groups with more than one rows:

For example, if the table is

col1 col2 col3
1      1      1
1      1      2
1      2      1
2      2      1
2      2      3
2      1      1

I am interested in finding groups by col1 and col2 with more than one rows, which are:

col1 col2 col3
1      1      1
1      1      2

and

col1 col2 col3
2      2      1
2      2      3

I was wondering how to write a SQL query for that purpose? Is the following the best way to do that?

First get the col1 and col2 values of such groups:

SELECT col1 col2 COUNT(*)
FROM mytable
GROUP BY col1, col2
HAVING COUNT(*) > 1

Then based on the output of the previous query, manually write a query for each group:

SELECT *
FROM mytable
WHERE col1 = val1 AND col2 = val2

If there are many such groups, then I will have to manually write many queries, which can be a disadvantage.

I am using SQL Server.

Thanks.

CodePudding user response：

This is a common problem. One solution is to get the "keys" in a derived table and join to that to get the rows.

declare @test as table (col1 int, col2 int, col3 int)
insert into @test values (1,1,1),(1,1,2),(1,2,1),(2,2,1),(2,2,3),(2,1,1)

select t.*
from @test t
inner join (
    select col1, col2
    from @test
    group by col1, col2
    having count(*) > 1
    ) k
on k.col1 = t.col1 and k.col2 = t.col2

col1        col2        col3
----------- ----------- -----------
1           1           1
1           1           2
2           2           1
2           2           3

CodePudding user response：

The window function sum() over() may help here

Example

with cte as (
Select *
      ,Cnt = sum(1) over (partition by Col1,Col2) 
 From YourTable
)
Select *
 From  cte 
 Where Cnt>=2

Results

Another option (less performant)

Select top 1 with ties *
 From  YourTable
 Order By case when sum(1) over (partition by Col1,Col2) > 1 then 1 else 2 end

Results