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