1

I'm trying to find the earliest created_at date of the latest category for all products. I've attached a sample table below:

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(6) UNSIGNED NOT NULL,
  `name` varchar(200) NOT NULL,
  `price` int NOT NULL,
  `category` varchar(200) NOT NULL,
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `products` VALUES
  (1, 'Apple', '750', 'Fruit', '2022-01-01 09:00:00'),
  (2, 'Banana', '1000', 'Fruit', '2022-01-01 09:00:00'),
  (3, 'Clementine', '1200', 'Fruit', '2022-01-01 09:00:00'),
  (4, 'Apple', '600', 'Vegetable', '2022-01-02 09:00:00'),
  (5, 'Banana', '900', 'Fruit', '2022-01-02 09:00:00'),
  (6, 'Apple', '750', 'Fruit', '2022-01-03 09:00:00'),
  (7, 'Banana', '900', 'Vegetable', '2022-01-03 09:00:00'),
  (8, 'Banana', '1000', 'Vegetable', '2022-01-04 09:00:00'),
  (9, 'Banana', '600', 'Fruit', '2022-01-05 09:00:00'),
  (10, 'Banana', '1000', 'Fruit', '2022-01-06 09:00:00');

So for example, because "Banana" was changed from a vegetable to a fruit in row 9, and hasn't had it's category changed since then, that's the date I'd like to get for that product.

In theory, finding each time the category column was altered for "Banana":

id product.name category "in_category_since"
2 Banana Fruit 2022-01-01 09:00:00
7 Banana Vegetable 2022-01-03 09:00:00
9 Banana Fruit 2022-01-05 09:00:00

Returning the latest of those instances:

id product.name category "in_category_since"
9 Banana Fruit 2022-01-05 09:00:00

And then doing this for each product name:

id product.name category "in_category_since"
6 Apple Fruit 2022-01-03 09:00:00
9 Banana Fruit 2022-01-05 09:00:00
3 Clementine Fruit 2022-01-01 09:00:00

As I've highlighted in the results in the image underneath, from this table I'd like to return the following rows:

(I'm using MySQL 8.0, not 5.6 as shown in the screenshot if this makes things easier.)

Example SQL Fiddle

0

1 Answer 1

0

First we will create a cte with the current category and the previous one. Then we will only return the rows where there was a change, or is the first. Then we get the maximum date grouped by name. This returns the latest change of category.

WITH cte as (
SELECT name, created_at as "in_category_since", category,
         LAG(category, 1)
                OVER (PARTITION BY Name ORDER BY created_at ASC)
         as previous_category
FROM products AS p
)
select name, MAX(in_category_since) as in_category_since
from cte as c
where c.category <> c.previous_category
OR c.previous_category is null
group by name
7
  • I want to return 2022-01-05 09:00:00 as this is the date of the latest category change from vegetable to fruit. Dec 1, 2022 at 9:29
  • Hi, I've updated my post, hopefully this explains things better. Dec 1, 2022 at 10:03
  • 1
    Hi Peter, as per the "Banana" example in my recent edit, even if a Product has been in the same category previously, I only want the earliest instance of that most recent change. Dec 1, 2022 at 10:35
  • I have edited the answer, this gives me the right answers
    – Peter
    Dec 1, 2022 at 11:42
  • 1
    That works! Thank you :) Dec 1, 2022 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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