Full disclosure I have posted a greater scoped question at SO a couple of days ago with a bounty (no answers yet).

Scope here

How can a CTE be used to find a match with the most elements? For the order 403 i want to find out

  • which pricerule is an INTERSECT for all features ordered (FeatureId in (1,2,3))
  • for a given featureId (1).

For FeatureId = 1 and OrderId=403 the best matching pricerule is PriceRuleId = 103.

From table OrderPositions OrderPositions

And table FeatureCombinations


I want to find each set (pair) with the most elements in both tables.

SQL Statemens to create the tables with records

Create tables

CREATE TABLE FeatureCombinations(
  PriceRuleId INTEGER,
  FeatureId   INTEGER   

CREATE TABLE OrderPositions(
  OrderId   INTEGER,   
  FeatureId   INTEGER

Create records

INSERT INTO FeatureCombinations (PriceRuleId, FeatureId)
VALUES (101, 1), (102, 1), (102, 2),
       (103, 1), (103, 2),(103, 3), 
       (105, 7), (106, 7), (106, 8); 
INSERT INTO OrderPositions(Id, OrderId, FeatureId)
VALUES (211, 401, 1), (221, 402, 1),(222, 402, 2),
(231, 403, 1),(232, 403, 2),(233, 403, 3);         

This is my query attempt but it runs forever so it must be wrong

-- DANGER  runs forever
WITH RECURSIVE cte_OrderPositions (PriceRuleId, FeatureId) AS (
    SELECT fc.PriceRuleId, op.FeatureId
    FROM OrderPositions op LEFT JOIN FeatureCombinations fc
    ON op.FeatureId = fc.FeatureId
    WHERE op.OrderId = 403 
    SELECT c.PriceRuleId, op.FeatureId
    FROM OrderPositions op
    JOIN cte_OrderPositions c ON c.FeatureId = op.FeatureId 
    -- DANGER  runs forever

SELECT * FROM cte_OrderPositions;
-- DANGER  runs forever


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.