Rewrite a sql query for better optimization [closed]

I have a table that has the following data

id	orderid
1	0
1	1
1	2
2	0
3	0
3	1

An id can have multiple order ids. If an id has only a single row with orderid 0 then it indicates that the order is not placed yet. I have find all the ids for which orders are not placed yet.

Here’s what I came up with

Select *
From (
    Select
      id,
      orderId,
      Count(id) Over (partition by id) 'cntId'
    From table
) a
Where a.cntId = 1
and a.Orderid = 0

Is there a better way to write this query? I would appreciate any help.

Answer

You could try it like this

SELECT id
FROM yourTable
GROUP BY id
HAVING count(*)=1
       and max(orderid)=0;