15,957,998 members
See more:
I have something like this
column1 column2
a 1
a 1
a 2
b 5
b 5
b 4
I want something like this
column1 column2
a 1
a 1
b 5
b 5
Posted

## Solution 1

the following prints duplicated rows uniquely.

SQL
```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.

SQL
```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```

v2
wizy@2020 1-Feb-14 2:48am
It seems to be working. but it gives me this
a 1
b 5
a 1
a 1
b 5
b 5

Peter Leow 7-Feb-14 5:03am
All's well that ends well. +5!

## Solution 2

SQL
```SELECT t1.column1, t1.column2 FROM tablename t1
WHERE
(
SELECT COUNT(t2.column2) FROM tablename t2 WHERE
t1.column1=t2.column1 AND t1.column2=t2.column2
) > 1```

Karthik_Mahalingam 7-Feb-14 5:05am
same 2 you as well :)

## Solution 3

try this

SQL
```select t2.* from
(
select column1,column2 from ta group by column1,column2 having count(*)>1
) t1
join ta t2 on
t1.column1 = t2.column1 and
t1.column2 = t2.column2```

Live Demo: fiddle[^]