[SOLVED] Mysql GROUP BY ...but I want the oldest one

Mysql GROUP BY ...but I want the oldest one

I would like to group my results by category when there is one but also that the user in the group is the oldest, but I can't decide which one...

|NAME  |DATE      |CATEGORY |ID
|bill  |2021-12-24|NULL     |6
|adrian|2021-12-23|NULL     |4
|steeve|2021-12-20|20       |8
|jak   |2021-12-14|NULL     |5
|peter |2021-12-01|10       |3
|rick  |2014-10-23|10       |2
|jean  |2012-12-12|10       |1
|bob   |2000-12-26|20       |7
|arnold|1950-12-10|20       |9

Here is my request to reproduce the problem, in real it is much more complex but to illustrate the problem it will be enough

SELECT * FROM my_table GROUP BY ifnull(category,id) ORDER BY date DESC

the result obtained is not what I would like because the user who comes out of the grouping is not the oldest

|NAME  |DATE      |CATEGORY |ID
|bill  |2021-12-24|NULL     |6
|adrian|2021-12-23|NULL     |4
|jak   |2021-12-14|NULL     |5
|jean  |2012-12-12|10       |1
|bob   |2000-12-26|20       |7

For the category 20 I expect to get arnold not bob

here is a real dataset for Mysql if it can help to solve this dark story

CREATE TABLE `my_table` (
  `name` varchar(20) CHARACTER SET utf8mb3 NOT NULL,
  `date` date NOT NULL,
  `category` int(20) DEFAULT NULL,
  `id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test` (`name`, `date`, `category`, `id`) VALUES
('jean', '2012-12-12', 10, 1),
('rick', '2014-10-23', 10, 2),
('peter', '2021-12-01', 10, 3),
('adrian', '2021-12-23', NULL, 4),
('jak', '2021-12-14', NULL, 5),
('bill', '2021-12-24', NULL, 6),
('bob', '2000-12-26', 20, 7),
('steeve', '2021-12-20', 20, 8),
('arnold', '1950-12-10', 20, 9);

Solution

Using ROW_NUMBER we can try:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY COALESCE(category, id)
                                 ORDER BY date DESC) rn
    FROM test
)

SELECT name, date, category, id
FROM cte
WHERE rn = 1;