How to retrieve duplicate rows/records in a table without using DISTINCT and GROUP BY clause?

Questions by Shilpaa.S

Showing Answers 1 - 4 of 4 Answers

Louis

  • Jul 13th, 2011
 

Code
  1. SELECT *

  2. FROM   &table_name a

  3. WHERE  rowid > (SELECT min (rowid)

  4.                 FROM   &table_name b

  5.                 WHERE  a.pk = b.pk);

  Was this answer useful?  Yes

RAJASEKHAR VINJAMURI

  • Aug 2nd, 2011
 

Select *
From table_name
Group By col1, col2, col3....
Having count(*) > 1;

  Was this answer useful?  Yes

You can use union

A union result will eliminate the duplicate rows ( for info, if one wants the duplicate rows preserved, that the UNION ALL should be used).

For example we have a table called test_table with 2 columns
create test_table
(
col1 varchar2(10),
col2 varchar2(5));

insert into test_table values ('abc', 'ddd');
insert into test_table values ('abc', 'ddd');
insert into test_table values ('efg', 'hhh');
insert into test_table values ('efg', 'hhh');

The folowing :

select * from test_table
union
select * from test_table;

will return
col1 / col2
abc / ddd
efg / hhh

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions