How do I query duplicate rows using two columns

the following prints duplicated rows uniquely.

select col1, col2 from
(select ROW_NUMBER() over(PARTITION by col1, col2 order by col1 asc) as rn, col1, col2 from T1) a
where rn=2

row_number() function assigns row number to each row of result set according to the 'over' clause. in this example, the result set is partitioned by <col1,col2> tuples. each <col1,col2> group is row-numbered within its group, such as:
row_no col1 col2
1 a 1
2 a 1
1 a 2
1 b 5
2 b 5
1 b 4

if you have another <a,1> tuple in your table, its row number would be 3. therefore, if I select the rows with the row number 2, that means I select row instances having count of at least 2. the new result set is unique in terms of <col1,col2> tuples, because there is only one instance within the partition with the row number 2.


if you want all of the duplicated rows, you can join the result with the original table.

select T1.col1, T1.col2
from T1 inner join
(select col1, col2 from
(select ROW_NUMBER() over(PARTITION by col1, col2 order by col1 asc) as rn, col1, col2 from T1) a
where rn=2) b
on T1.col1 = b.col1 and T1.col2 = b.col2