Greatest-n-per-group Query Example

QUERY = """
CREATE TABLE purchase_orders (
  id INT NOT NULL AUTO_INCREMENT,
  number VARCHAR(255) NOT NULL,
  PRIMARY KEY(id)
) ENGINE INNODB;


CREATE TABLE email_messages (
  id INT NOT NULL AUTO_INCREMENT,
  from_address VARCHAR(255) NOT NULL,
  to_address VARCHAR(255) NOT NULL,
  subject VARCHAR(255),
  body TEXT,
  PRIMARY KEY(id)
) ENGINE INNODB;


CREATE TABLE purchase_order_emails (
  purchase_order_id INT NOT NULL,
  email_id INT NOT NULL,
  `status` ENUM('pending', 'delivered', 'bounced'),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(purchase_order_id, email_id, `status`),
  FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders (id),
  FOREIGN KEY (email_id) REFERENCES email_messages (id)
) ENGINE INNODB;
  
  
INSERT INTO purchase_orders VALUES (NULL, "PF1");

INSERT INTO email_messages VALUES (NULL, "[email protected]", "[email protected]", "PO - PF1", "This is my purchase order");
INSERT INTO purchase_order_emails VALUES (1, 1, "pending", NULL);

INSERT INTO email_messages VALUES (NULL, "[email protected]", "[email protected]", "PO - PF1", "This is my purchase order");
INSERT INTO purchase_order_emails VALUES (1, 2, "pending", NULL);

SELECT SLEEP(2);
INSERT INTO purchase_order_emails VALUES (1, 2, "delivered", NULL);
SELECT SLEEP(3);
INSERT INTO purchase_order_emails VALUES (1, 1, "bounced", NULL);

INSERT INTO purchase_orders VALUES (NULL, "PF2");
SELECT SLEEP(1);
INSERT INTO email_messages VALUES (NULL, "[email protected]", "[email protected]", "PO - PF2", "Please deliver this.");
INSERT INTO purchase_order_emails VALUES (2, 3, "pending", NULL);
"""

QUERY = """
SELECT 
  purchase_orders.*, 
  email_messages.to_address AS email_address,
  purchase_order_emails.status AS email_status, 
  purchase_order_emails.created_at AS email_status_updated_at
FROM purchase_orders
LEFT OUTER JOIN purchase_order_emails 
  ON purchase_order_emails.purchase_order_id = purchase_orders.id
INNER JOIN email_messages ON email_messages.id = purchase_order_emails.email_id
WHERE purchase_order_emails.created_at = (
  SELECT MAX(created_at) FROM purchase_order_emails 
  WHERE purchase_order_id = purchase_orders.id AND email_id = email_messages.id
);


SELECT 
  purchase_orders.*,
  GROUP_CONCAT(
    email_messages.to_address, 
    ' (', 
    purchase_order_emails.status,
    ' at ',
    purchase_order_emails.created_at,
    ')'      
    ORDER BY purchase_order_emails.created_at ASC
    SEPARATOR ', '
  ) AS emails
FROM purchase_orders
LEFT OUTER JOIN purchase_order_emails 
  ON purchase_order_emails.purchase_order_id = purchase_orders.id
INNER JOIN email_messages ON email_messages.id = purchase_order_emails.email_id
WHERE purchase_order_emails.created_at = (
  SELECT MAX(created_at) 
  FROM purchase_order_emails 
  WHERE purchase_order_id = purchase_orders.id 
    AND email_id = email_messages.id
)
GROUP BY purchase_orders.id;
"""