Rewrite a sql query for better optimization-CodePudding

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.

CodePudding user response：

You could try it like this

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

CodePudding user response：

If you want to go the aggregation approach, I prefer:

SELECT id
FROM yourTable
GROUP BY id
HAVING MIN(orderid) = MAX(orderid) AND MIN(orderid) = 0;

This query should benefit from an index on (id, groupid).

My answer doesn't select the groupid value, but we already know that this value would have to be zero for the entire result set.

CodePudding user response：

If you KNOW the sequential ordering of the / order IDs will always be 0, 1, 2, etc, and you have an index on (id, orderid), I would self-join for so it does not need to query for those IDs that may have 100 orders

select
      yt.id
   from
      YourTable yt
         LEFT JOIN YourTable yt2
            on yt.id = yt2.id
           and yt2.orderid = 1
   where
          yt.orderid = 0
      AND yt2.id is null

So it will only ever care about an order ID = 0 or 1 and return only those where no orderID = 1 is found.