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

Questions by Shilpaa.S

Showing Answers 1 - 13 of 13 Answers

Louis

  • Jul 13th, 2011
 

Code
  1. SELECT *

    2. 

  2. FROM   &table_name a

    3. 

  3. WHERE  rowid > (SELECT min (rowid)

    4. 

  4.                 FROM   &table_name b

    5. 

  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

fire_starter

  • Oct 9th, 2011
 

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

May

  • Apr 28th, 2021
 

It clearly mentioned "without using group by".

  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