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.

Answer 1

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.

Answer 2

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