Post reply

Better way to set order of selections/precedence order?

  • June 26, 2025 at 11:59 pm

    #4613903

    Our application sells products from various distributors.

    Each buyer has a "precedence order" when it comes to their most to their least favorites distributors.

    For ex:  BuyerA can have precedence order of (Vendor2 is favorite, Vendor 1 is second choice, Vendor 3 is last choice) assuming that there are only 3 potential distributors.

    BuyerB can have a different precedence order for those 3 potential distributors.

    I have sample data for 5 products, 3 vendors where the Buyer predefines his precedence order (VendorID2 is favorite,

    VendorID1 is second choice, vendor 3 is last choice).

    I am looking for ideas and suggestions to do this better.  The where clauses can get ugly when the number of potential vendors grow...

    Thank you!

    Attachments:
    You must be logged in to view attached files.

  • June 27, 2025 at 7:46 am

    #4614052

    Something like this?

    DROP TABLE IF EXISTS #product;
    DROP TABLE IF EXISTS #VendorRank;

    --Product table and data
    CREATE TABLE #Product
    (
        ProductID INT
      , ProductSKU INT
      , VendorID INT
    );

    INSERT #Product
    (
        ProductID
      , ProductSKU
      , VendorID
    )
    VALUES
    (10, 100, 1)
    , (11, 100, 2)
    , (12, 100, 3)
    , (13, 200, 3)
    , (14, 300, 1)
    , (15, 400, 1)
    , (16, 400, 2)
    , (17, 400, 3)
    , (18, 500, 1)
    , (19, 500, 3);

    --Vendor rank table and data
    CREATE TABLE #VendorRank
    (
        VendorID INT
      , AssignedRank INT
    );

    INSERT #VendorRank
    (
        VendorID
      , AssignedRank
    )
    VALUES
    (2, 1)
    , (1, 2)
    , (3, 3);

    --Get ranked results
    WITH ranked
    AS (SELECT p.ProductID
             , p.ProductSKU
             , p.VendorID
             , vr.AssignedRank
             , rn = ROW_NUMBER() OVER (PARTITION BY p.ProductSKU ORDER BY vr.AssignedRank)
        FROM #Product p
            JOIN #VendorRank vr
                ON vr.VendorID = p.VendorID)
    SELECT r.ProductID
         , r.ProductSKU
         , r.VendorID
         , r.AssignedRank
         , r.rn
    FROM ranked r
    WHERE r.rn = 1
    ORDER BY r.ProductSKU;

  • June 27, 2025 at 2:44 pm

    #4614245

    Thank you so much, Phil.  Your logic makes it more adaptable to any number of vendors...

    Far better than my awkward, unwieldy WHERE clauses...

    Many thanks!

  • June 30, 2025 at 11:04 am

    #4615459

    Thanks for posting back. Glad it helped you.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply