How can I do a query that shows any row that have more than twice in the same table without using GROUP BY or COUNT I want to select all same item that occurs in the same table twice without GROUP BY or COUNT sentence. How to write the query?

For example, if the table is:

City
---------
New York
Los Angeles
London
Washington
London
Peking
London
San Fransisco
New York
Tokyo
Dubai


The result set should be like that:

City
---------
New York
London


(because 'New York' occurs 3 times and 'London' occurs twice in the original table)
with data as
(
select 'New York' as city from dual union all
select 'Los Angeles' from dual union all
select 'London' from dual union all
select 'Washington' from dual union all
select 'London' from dual union all
select 'Peking' from dual union all
select 'London' from dual union all
select 'San Fransisco' from dual union all
select 'New York' from dual union all
select 'Tokyo' from dual union all
select 'Dubai' from dual
)
select
  distinct city
from
(
  select
    city,
    row_number() over (partition by city order by null) as rn
  from
    data
) 
where
  rn &gt; 1
;

CITY
-------------
London
New York


No GROUP BY, no COUNT.

But we have DISTINCT, ROW_NUMBER(), PARTITION BY, ORDER BY.

Is it simpler/better than?

with data as
(
select 'New York' as city from dual union all
select 'Los Angeles' from dual union all
select 'London' from dual union all
select 'Washington' from dual union all
select 'London' from dual union all
select 'Peking' from dual union all
select 'London' from dual union all
select 'San Fransisco' from dual union all
select 'New York' from dual union all
select 'Tokyo' from dual union all
select 'Dubai' from dual
)
select
  city
from
  data
group by
  city
having
  count(*) &gt; 1
;


No, I guess.