Apply different sorting criteria as per table configuration

I have one mysql table "Routing" which contain 4 fields: Id, Rcost, Rpriority, Rmargin. We need to design a routing engine which will fetch the route as per below criteria: 1. Least Cost 2. Highest priority 3. Highest profitMargin

There different routing mechanisms will be listed with their priority 开发者_如何学Goin one diff table as per below rule .. 1=> Highest priority; 3=> Lowest priority; 0=> Not applicable.

if a given algorithm returns more than one route these chosen routes will be applied to the next algorithm, if multiple routes are again returned then these returned routes are again applied to the next algorithm until a single route is returned or no algorithms are left. If no algorithm is left then the first returned route in the list will be used.

Below is table content:

CREATE TABLE Routing (
        Id INT(10) NOT NULL auto_increment,
        Prefix VARCHAR(30) NOT NULL,
        Route INT(10) NOT NULL,
        Cost FLOAT NULL DEFAULT 0,
        Priority INT(10) NULL DEFAULT 0,
        ProfitMargin INT(10),
        Quality INT(10),
        PRIMARY KEY(Id)
);

Table data :

INSERT INTO Routing (Cost, Priority, ProfitMargin, Quality) VALUES (10, 20, 8, 4);
INSERT INTO Routing (Cost, Priority, ProfitMargin, Quality) VALUES (10, 20, 8, 4);
INSERT INTO Routing (Cost, Priority, ProfitMargin, Quality) VALUES (5, 18, 10, 4);
INSERT INTO Routing (Cost, Priority, ProfitMargin, Quality) VALUES (5, 18, 8, 4);
INSERT INTO Routing (Cost, Priority, ProfitMargin, Quality) VALUES (5, 19, 10, 4);
INSERT INTO Routing (Cost, Priority, ProfitMargin, Quality) VALUES (10, 20, 8, 4);
INSERT INTO Routing (Cost, Priority, ProfitMargin, Quality) VALUES (10, 20, 8, 4);
INSERT INTO Routing (Cost, ProfitMargin, Quality) VALUES (5, 10, 4);

The another table is carrying information about priority of application of sorting mechanism and their applicability as per rule described above.

Other table:

DROP TABLE IF EXISTS RoutingCriteria;
CREATE TABLE RoutingCriteria (
                Id INT(10) NOT NULL auto_increment,
                Cost TINYINT,
                Priority TINYINT,
                Quality TINYINT,
                ProfitMargin    INT(4),
                PRIMARY KEY(Id)
);

Table data:

insert into RoutingCriteria (Cost, Priority, Quality, ProfitMargin) VALUES(1, 2, 3, 4);
insert into RoutingCriteria (Cost, Priority, Quality, ProfitMargin) VALUES(4, 1, 3, 2);
insert into RoutingCriteria (Cost, Priority, Quality, ProfitMargin) VALUES(0, 2, 3, 0);
insert into RoutingCriteria (Cost, Priority, Quality, ProfitMargin) VALUES(1, 0, 3, 4);

We need to get route if we use different routeCriteria values.

Given the info this is i can come up with to suggest:

SELECT * 
  FROM Routing 
 WHERE Priority > 0         -- excluding Not applicable
 ORDEY BY Cost ASC          -- Least Cost
        , Priority ASC      -- 1=> Highest; 3=> Lowest
        , ProfitMargin DESC -- Highest profitMargin
 LIMIT 1                    -- return only 1 result, i.e the first/best