min and count without limit

I need to select a receipt with 3 conditions:

1. total receipt > 50 (receipt.total)
2. associated purchases doesn’t include item “cucumbers” (product.pname)
3. the number of suppliers (product.sid) of associated products is the lowest

I have the following tables set up:

Receipt (bid, rdate, rtime, ptype, total)
Purchase (bid, rdate, rtime, code, units)
Product (code, pname, descr, utype, uprice, manu, sid)

sample data:

INSERT INTO Product (code, pname, descr, utype, uprice, manu, sid) VALUES
        (987, 'Tomatoes',       'Vegetable',  'Kg',  5.99,  'manufacturer1', 111),
        (876, 'Cucumbers',      'Vegetable',  'Kg',  4.99,  'manufacturer2', 222),
        (765, 'Cornflakes',     'Cornflakes', 'Box', 15.9,  'manufacturer2', 222),
        (654, 'Camembert',      'Cheese',     'Box', 12.50, 'manufacturer2', 111),
        (543, 'sweet potato',   'Vegetable',  'Kg',  16.40, 'manufacturer3', 333),
        (432, 'red pepper',     'Vegetable',  'Kg',  15.99, 'manufacturer1', 111);
INSERT INTO Receipt (bid, rdate, rtime, ptype, total) VALUES
        (989, '18/3/2020', '10:00', 'Cash', 126.51),
        (989, '16/7/2020', '12:30', 'Credit',0),
        (989, '15/7/2020', '15:35', 'Credit',0),
        (878, '17/3/2020', '8:30',  'Cash', 60.47),
        (878, '22/7/2020', '7:00',  'Credit',0),
        (767, '13/7/2020', '22:00', 'Cash',0),
        (767, '10/3/2020', '20:30', 'Cash',42.97),
        (767, '14/5/2020', '14:25', 'Credit',0);


INSERT INTO Purchase (bid, rdate, rtime, code, units) VALUES
        (989, '18/3/2020', '10:00', 987, 5),
        (989, '18/3/2020', '10:00', 876, 3),
        (989, '18/3/2020', '10:00', 543, 4),
        (989, '18/3/2020', '10:00', 432, 1),
        (878, '17/3/2020', '8:30',  654, 1),
        (878, '17/3/2020', '8:30',  432, 3),
        (767, '10/3/2020', '20:30', 654, 2),
        (767, '10/3/2020', '20:30', 987, 3);

So far I have the following but it is not getting the receipt with the lowest number of sid:

SELECT Receipt.rdate, Receipt.rtime, Receipt.bid
FROM Receipt NATURAL JOIN Purchase NATURAL JOIN Product
WHERE total > 50 and 
NOT EXISTS(
    SELECT * FROM Product NATURAL JOIN Purchase
    WHERE Receipt.bid = Purchase.bid and Receipt.rdate = Purchase.rdate and Receipt.rtime = Purchase.rtime and Product.code = '876')
GROUP BY Receipt.rdate, Receipt.rtime, Receipt.bid
HAVING COUNT(DISTINCT Product.sid) = (SELECT MIN(mycount) 
FROM (SELECT sid,COUNT(DISTINCT Product.sid) mycount from product group by product.sid) ct);

I’m not allowed to use order by and limit. What am I doing wrong?

Answer

Without ORDER BY:

with cte as 
 ( SELECT Receipt.rdate, Receipt.rtime, Receipt.bid,
      COUNT(DISTINCT Product.sid) as cnt, -- count per bid
      MIN(COUNT(DISTINCT Product.sid))    -- minimum count over rows
      OVER () as min_count                -- PARTITION BY needed???
   FROM Receipt NATURAL JOIN Purchase NATURAL JOIN Product
   WHERE total > 50 and 
   NOT EXISTS(
       SELECT * FROM Product NATURAL JOIN Purchase
       WHERE Receipt.bid = Purchase.bid
         and Receipt.rdate = Purchase.rdate
         and Receipt.rtime = Purchase.rtime and Product.code = '876')
   GROUP BY Receipt.rdate, Receipt.rtime, Receipt.bid
 )
select *
from cte
where cnt = min_count

Simplifying the NOT EXISTS logic:

with cte as 
 ( SELECT Receipt.rdate, Receipt.rtime, Receipt.bid,
      COUNT(DISTINCT Product.sid) as cnt, -- count per bid
      MIN(COUNT(DISTINCT Product.sid))    -- minimum count over rows
      OVER () as min_count                -- PARTITION BY needed???
   FROM Receipt NATURAL JOIN Purchase NATURAL JOIN Product
   WHERE total > 50 
   GROUP BY Receipt.rdate, Receipt.rtime, Receipt.bid
   -- will be > 0 when cucumbers exist
   HAVING count(case when product.pname = 'cucumbers' then 1 end) = 0
 )
select *
from cte
where cnt = min_count