Select column value with min value partition by another column but NOT repeat ranking-CodePudding

I am looking to fetch the eqp based on min distance by contract, but if an eqp is taken by a contract then it shouldn't be considered.

Table: T1

id	contract	eqp	distance
1	123	A	2
2	123	B	5
3	123	C	20
4	124	A	2
5	124	B	7
6	124	C	11

I used rank and it gives me same rank for two different contract but I would not want to use the rank for a prior record already taken.

SELECT
id,contract,eqp,rk
FROM
(
SELECT id,contract,eqp,
RANK() OVER (PARTITION BY contract ORDER BY distance) AS rk
FROM t1
) a
WHERE rk=1

What I get is below,

id	contract	eqp	distance	rk
1	123	A	2	1
4	124	A	2	1

Expected Output:

id	contract	eqp	distance	rk
1	123	A	2	1
5	124	B	7	1

CodePudding user response：

This is an attempt to solve this problem with a single recursive query.

WITH RECURSIVE contracts AS (
  SELECT *, DENSE_RANK() OVER (ORDER BY contract) AS contract_num
  FROM t1

), cte AS (
  SELECT *, CAST(CONCAT('.', eqp) AS char(1000)) AS used_eqp, 1 as rn
  FROM contracts
  WHERE contract_num = 1 AND distance = (
    SELECT MIN(distance) FROM contracts WHERE contract_num = 1
  )
  UNION ALL
  SELECT t1.*, CONCAT(cte.used_eqp, '.' , t1.eqp) AS used_eqp, 
    (   WITH sub AS (
            SELECT t0.id, RANK() OVER (ORDER BY distance) rn
            FROM contracts t0
            WHERE t0.contract_num = cte.contract_num   1 
              AND CONCAT(used_eqp, '.') NOT LIKE CONCAT('%.', t0.eqp ,'.%') 
              AND cte.rn = 1
        ) SELECT rn FROM sub WHERE sub.id = t1.id LIMIT 1
    ) AS rn
  FROM contracts t1
  JOIN cte ON t1.contract_num = cte.contract_num   1 
          AND CONCAT(used_eqp, '.') NOT LIKE CONCAT('%.', t1.eqp ,'.%') 
          AND cte.rn = 1
)
SELECT id,contract,eqp,distance 
FROM cte 
WHERE rn = 1

Here, recursion accumulates the previously used eqp values into the used_eqp string, separated by a dot. As a result, the eqp value will be unique.

db<>fiddle

CodePudding user response：

The task is iterative. It cannot be solved by single query.

Possible solution:

CREATE PROCEDURE proc ()
BEGIN
CREATE TABLE tmp LIKE t1;
REPEAT
    INSERT INTO tmp
    SELECT t1.*
    FROM t1
    LEFT JOIN tmp t2 ON t1.contract = t2.contract   -- for huge table
    LEFT JOIN tmp t3 ON t1.eqp = t3.eqp             -- use NOT EXISTS
    WHERE t2.id IS NULL
      AND t3.id IS NULL
    ORDER BY distance LIMIT 1;     -- adjust to needed priority
UNTIL NOT ROW_COUNT() END REPEAT;
SELECT * FROM tmp;
DROP TABLE tmp;
END

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=c9563e1d2e9884dc607a52f10ff401bb