MySQL query to select a record from each group that meets a user-defined criterion

Question

I am looking for a query that selects a record from each group that meets a user-defined criterion. I can explain with the following illustrative table:

CREATE TABLE sample_table
(
  id INT UNSIGNED AUTO_INCREMENT,
  categoryID INT,
  weight INT,

  PRIMARY KEY(id)  
);

INSERT INTO sample_table(categoryID, weight) VALUES(1, 3), (1, 5), (1, 2), (2, 5), (2, 3), (2, 9), (3, 5), (3, 3), (3, 3);

A simple GROUP BY categoryID query returns the first record in each group, as shown below:

SELECT * FROM sample_table GROUP BY categoryID;
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  1 |          1 |      3 |
|  4 |          2 |      5 |
|  7 |          3 |      5 |
+----+------------+--------+

To return the last record in each group we can use the approach suggested here:

SELECT * FROM sample_table WHERE id IN (SELECT MAX(id) FROM sample_table GROUP BY categoryID);
+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  3 |          1 |      2 |
|  6 |          2 |      9 |
|  9 |          3 |      3 |
+----+------------+--------+

However, what I want to do is to select the record which the highest value for the weight field in each group. My output, therefore, should be:

+----+------------+--------+
| id | categoryID | weight |
+----+------------+--------+
|  2 |          1 |      5 |
|  6 |          2 |      9 |
|  7 |          3 |      3 |
+----+------------+--------+

---

Kindly suggest the GROUP BY categoryID query which will yield the above output.

Answer 1

The "modern" way of doing this on MySQL 8+ would be to use ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY categoryID ORDER BY weight DESC) rn
    FROM sample_table
)

SELECT id, categoryID, weight
FROM cte
WHERE rn = 1;

On earlier versions, you may use a join approach:

SELECT t1.id, t1.categoryID, t1.weight
FROM sample_table t1
INNER JOIN
(
    SELECT categoryID, MAX(weight) AS max_weight
    FROM sample_table
    GROUP BY categoryID
) t2
    ON t2.categoryID = t1.categoryID AND
       t2.max_weight = t1.weight;

Answer 2

I would go for:

SELECT id, categoryID, weight
FROM sample_table ret
WHERE NOT EXISTS (
  SELECT *
  FROM sample_table witness
  WHERE witness.categoryID = ret.categoryID
    AND witness.weight > ret.weight
);