Mysql ГРУППИРУЕТСЯ ПО ...но я хочу самый старый

#group-by #sql-order-by #mariadb-10.6

#группа-по #sql-порядок-по #мариадб-10,6

Вопрос:

Я хотел бы сгруппировать свои результаты по категориям, когда они есть, но также и то, что пользователь в группе самый старый, но я не могу решить, какой из них…

 |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

Вот моя просьба воспроизвести проблему, в реальности она намного сложнее, но для иллюстрации проблемы этого будет достаточно

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

полученный результат не то, что мне хотелось бы, потому что пользователь, который выходит из группы, не самый старый

 |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

Для категории 20 я ожидаю получить arnold не bob

вот реальный набор данных для Mysql, если он может помочь решить эту темную историю

 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);

Ответ №1:

Используя ROW_NUMBER , мы можем попробовать:

 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;