MySQL Select previous row that matches condition

Question

I am using MySQL 5.7 and I have 2 tables where relevant data looks like this: SQL was exported using mysql workbench, let me know if it doesn't work or if it doesn't make sense:

CREATE DATABASE  IF NOT EXISTS `blog`
USE `blog`;


DROP TABLE IF EXISTS `article`;

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated` bit(1) NOT NULL DEFAULT b'0',
  `date` date NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_article_1_idx` (`category_id`),
  CONSTRAINT `fk_article_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;


LOCK TABLES `article` WRITE;

INSERT INTO `article` VALUES (1,_binary '','2020-02-01',2),(2,_binary '','2020-02-01',2),(3,_binary '','2020-02-02',2),(4,_binary '\0','2020-02-05',2),(5,_binary '','2020-02-06',2),(6,_binary '\0','2020-02-08',2),(7,_binary '\0','2020-02-11',2),(8,_binary '','2020-02-13',2),(9,_binary '','2020-02-16',3),(10,_binary '','2020-02-29',2),(11,_binary '','2020-03-01',2),(12,_binary '\0','2020-02-29',2);

UNLOCK TABLES;



DROP TABLE IF EXISTS `category`;

CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


LOCK TABLES `category` WRITE;

INSERT INTO `category` VALUES (1,'category_1'),(2,'category_2'),(3,'category_3'),(4,'category_4');

UNLOCK TABLES;

What I want to accomplish is get the articles(or even better a list of article pairs[updated-not_updated] that fit in a certain category that were not updated, but the direct previous(by this I mean previous date) article belonging to the same category was updated. If the dates are equal then additional ordering is by id.

For the examples sake I guess I'm interested in retrieving the articles 4(and 3), 6(and 5) and 12(and 10) since these are the ones that are not updated but the article posted right before them that was in the same category was updated.

This doesn't have to be happening all in one query(although that would be the preferred way). I'm using java with Spring native queries if it matters. I did think of getting all articles that were not updated and then check each record to see if it fits my criteria but it doesn't seem this is the right approach since I can get to maybe a couple of articles that were not updated and making the check could hurt performance and processing times. Any way I can do this directly from MySQL with a native query?

I hope my question is well formed, please ask for more details if they are needed. Column names have been changed but the structure is correct(in case you are wondering why they weird criteria for select)

Thanks in advance.

Reason for edit: provide create and insert startements(exported with mysql workbench)

Answer 1

With MySQL 8 it would be much simpler using window functions.

The problem is with your data that you have no sequential order, so we have to get one by using row number.

This way we can compare the row numbers from the coplete table and those with has was_updated = 0.

This will only work in MySQL because it holds the ORDER BY from the inner select in MariaDB you have to use LIMIT to keep the order.

CREATE TABLE articles
    (`id` int, `category` int, `was_updated` int, `date_added` varchar(10))
;
    
INSERT INTO articles
    (`id`, `category`, `was_updated`, `date_added`)
VALUES
    (1, 2, 1, '10-01-2020'),
    (2, 2, 1, '11-01-2020'),
    (3, 2, 0, '13-01-2020'),
    (4, 2, 0, '20-01-2020'),
    (5, 1, 1, '22-01-2020'),
    (6, 2, 1, '23-01-2020'),
    (7, 2, 1, '02-02-2020'),
    (8, 2, 0, '29-01-2020')
;

SELECT 
    a3.id
FROM
    (SELECT 
        a.*, @rown2:=@rown2 + 1 rn
    FROM
        articles a, (SELECT @rown2:=0) t1
    ORDER BY id) a2
    INNER JOIN
    (SELECT 
           id, rn,`category`
        FROM
            (SELECT 
                a.*, @rown:=@rown + 1 rn
            FROM
                articles a, (SELECT @rown:=0) t1
            ORDER BY id) a1
        WHERE
            `was_updated` = 0) a3
ON 

a2.rn  + 1 = a3.rn
AND 
a2.`category` = a3.`category`
WHERE a2.`was_updated` = 1

| id |
| -: |
|  3 |
|  8 |

db<>fiddle here