sqlsql-serverdatabasessismsbi

Rewrite a sql query for better optimization

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.

Solution

  • You could try it like this

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