How do I find duplicate values in Oracle without using HAVING/GROUP BY

Question

I need a query that selects a list of duplicated values that does not use HAVING COUNT function (this is due to a software constraint that only allows me to use SELECT, FROM, and WHERE).

In similar fashion to this:

select column_name, count(column_name)
from table
group by column_name
having count (column_name) > 1;

Answer 1

You can select duplicate column_name values using a window function

select * from (
  select column_name, count(*) over (partition by column_name) cnt
  from mytable 
) t1 where cnt > 1

here's another query using exists in case you can't use window functions

select * from mytable t1
where exists (
    select 1 from mytable t2
    where t2.column_name = t1.column_name
    and t2.id <> t1.id
)

Answer 2

I can think of rowid as an option:

select column_name from table a
where a.rowid >
any( select b.rowid
from table b
where a.column_name = b.column_name );

Here is an example

Answer 3

To purely use SELECT, FROM , WHERE (with additional DISTINCT):

 SELECT DISTINCT a.column_name FROM mytable as a, mytable as b
 WHERE a.rowID != b.rowID AND a.column_name = b.column_name;

Or, maybe better

 SELECT DISTINCT a.column_name FROM mytable as a, mytable as b
 WHERE a.rowID < b.rowID AND a.column_name = b.column_name;

This looks like an exercise in some of the database courses. However the answer is not useful in reality, I think. : )