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)
);
The table is populated as follows:
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.
Best Answer
fiddle
I.e. we enumerate rows in a group by
categoryID
ordering them byweight
decrease, then get 1st row in each group.