0

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.

1
  • A simple GROUP BY categoryID query returns the first record in each group No. It returns some record. Today this some occures to be "first". – Akina Dec 8 '20 at 4:29
0

select the record which the highest value for the weight field in each group.

WITH cte AS (SELECT id, categoryID, weight,
                    ROW_NUMBER() OVER (PARTITION BY categoryID ORDER BY weight DESC, id) rn
             FROM sample_table)
SELECT id, categoryID, weight
FROM cte
WHERE rn = 1;

fiddle

I.e. we enumerate rows in a group by categoryID ordering them by weight decrease, then get 1st row in each group.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.