Groupwise maximum record lookup for contracts and latest status

Question

I'm looking for help as I can't seem to get a handle on a SQL query.

I have two tables:

Contracts - where the list of contracts is kept along with the associated partners

CREATE TABLE `contracts` (
  `id` varchar(5) NOT NULL,
  `partner` varchar(12) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

locks - list of contract statuses

CREATE TABLE `locks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('unlock','templock','lock','permalock') DEFAULT NULL,
  `contractID` varchar(5) NOT NULL,
  `partnerID` varchar(12) NOT NULL,
  `stamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `user` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)

I am trying to get a list of the latest contract statuses for a given partner:

SELECT c.id, c.partner ,l.`type`, l.stamp, l.id 
FROM contracts c 
LEFT JOIN locks l
ON c.id = l.contractID 
WHERE partner="2000000301"
GROUP BY c.id ASC

I was able to get the result, but it doesn't indicate the last status correctly - it "takes" the first one occurring :(, and I need the last one.

I tried the sequential approach, i.e. I first made a query that retrieves the last state for the contract:

SELECT max(l.id), l.partnerID ,l.contractID ,l.stamp ,l.`type`  
FROM locks l
WHERE l.partnerID = "2000000301" and l.contractID ="35274";

It works fine, but if I tried to implement this to the main query, using 'LEFT JOIN(...)'

SELECT *
FROM contracts
LEFT JOIN (
  SELECT max(ll.id), ll.contractID, ll.partnerID, ll.stamp, ll.`type`
  FROM locks ll
  WHERE ll.partnerID = contracts.partner <-- error :(
) l
ON contracts.id = l.contractID 
WHERE partner="2000000301"
ORDER BY contracts.id ASC

Here there is a problem with the availability of the 'contractID' field from the 'contracts' table and I get an error :(

SQL Error [1054] [42S22]: (conn=180) Unknown column 'contracts.partner' in 'where clause'

I had already lost the concept of approach.

You cannot outer join using a *correlation*, and your sub-query is malformed, you are aggregating without a *group by* — Stu, Mar 01 '22 at 13:45
I understand that the subquery should return records with only the most recent statuses for a given partner and in this form, it should go to the main question. I have constructed such query: `SELECT l.partnerID ,l.contractID ,l.stamp ,l.type FROM locks l WHERE l.partnerID = "2000000301" GROUP BY l.contractID;` but it does not return the last status for the contract. — Paweł Banaś, Mar 01 '22 at 14:07
OK. I was able to put together a query that produces the result: each contractor and its last status change. `SELECT l.contractID, l.partnerID, l.stamp, l.type. FROM locks l INNER JOIN (SELECT contractID, max(id) as max_id FROM locks GROUP BY contractID) m ON l.contractID=m.contractID and l.id=m.max_id` — Paweł Banaś, Mar 01 '22 at 14:42

O. Jones · Accepted Answer · 2022-03-01T15:18:07.720

This is called a groupwise-maximum problem.

It looks like your locks table gets updated sometimes, and those updates change the stamp timestamp column. So your problem is to report out the latest -- most recent in time -- locks record for each contractID. Start with a subquery to determine the latest stamp for each contract.

                 SELECT MAX(stamp) stamp, contractID
                   FROM locks
                  GROUP BY contractID

Then use that subquery in your main query to choose the appropriate row of locks.

SELECT c.id ,c.partner ,l.stamp ,l.`type`
  FROM contracts c
  LEFT JOIN (
                 SELECT MAX(stamp) stamp, contractID
                   FROM locks
                  GROUP BY contractID
       ) latest ON c.contractID=latest.contractID  
  LEFT JOIN locks l   ON c.contractID = l.contractID
                     AND latest.stamp = l.stamp
 WHERE c.partner="2000000301"
 ORDER BY c.id ASC

Notice that the latest locks record is not necessarily the one with the largest id value.

This index will help the query's performance when your locks table is large, by enabling the subquery to do a loose index scan.

ALTER TABLE locks ADD INDEX contractid_stamp (contractID, stamp);

And, you don't need both a PRIMARY KEY and a UNIQUE KEY on the same column. The PRIMARY KEY serves the purpose of guaranteeing uniqueness. Putting both keys on the table slows down INSERTs for no good reason.

Thank you, you have nicely elaborated this for me. You are correct in looking at the `stamp`, however, it may happen (and I assume such a situation) that the `stamp` for several records may be the same and then the problem arises which is last. Hence my approach via `id`, which always increases, when adding records to `locks`. I don't provide for changes in the `locks` records so, the highest `id` will always be the last one. I was thinking myself that surely this subquery must consist of one more query, and here you go. One more LEFT JOIN :) It's not a problem. It's a better solution. — Paweł Banaś, Mar 01 '22 at 17:07

score 0 · Answer 2 · answered Mar 01 '22 at 14:55

I think it worked :)

SELECT c.id ,c.partner ,l.stamp ,l.`type`
FROM contracts c
LEFT JOIN (
    SELECT l.contractID, l.partnerID, l.stamp, l.`type`
    FROM locks l INNER JOIN (
      SELECT contractID, max(id) as max_id
      FROM locks
      group by contractID
    ) m
    ON l.contractID=m.contractID and l.id=m.max_id  
) l
ON c.id = l.contractID 
WHERE c.partner="2000000301"
ORDER BY c.id ASC

Groupwise maximum record lookup for contracts and latest status

2 Answers2