Oracle optimization

Question

I want to select duplicate data in column 'egno' and not duplicate data in 'dsver'.

My source MySQL query is:

select * from test
where egno in (
  select egno
  from test
  group by egno
  having count (*) >1
)
group by dsver
having count (*) = 1

But it doesn't work on Oracle. So I wrote a new query:

select * from test
where egno in (
  select egno
  from test
  group by egno
  having count (*) >1
)
and dsver in (
  select dsver
  from test
  group by dsver
  having count (*) = 1
)

I think it will use more resources on the server. I need some suggestions on this query.

Answer 1

Use the COUNT() analytic function:

SELECT *
FROM   (
  SELECT t.*,
         COUNT(*) OVER ( PARTITION BY egno ) AS num_egno,
         COUNT(*) OVER ( PARTITION BY dsver ) AS num_dsver
  FROM   test t
)
WHERE num_egno > 1
AND   num_dsver = 1;