MYSQL Nearest row before with another column having a specific value

Question

I've been trying to LEFT JOIN a table to itself depending on the TIMESTAMP (nearest before this row) and another column STABILISATION (with value 1)

query for the test table:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `UID` varchar(40) NOT NULL,
  `CREATED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `STABILISATION` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`UID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

TRUNCATE `test`;
INSERT INTO `test` (`UID`, `CREATED`, `STABILISATION`) VALUES
('1',   '2014-03-12 09:22:41',  1),
('2',   '2014-03-12 09:24:20',  0),
('3',   '2014-03-12 09:24:35',  0),
('4',   '2014-03-12 09:24:39',  1),
('5',   '2014-03-12 09:24:41',  0),
('6',   '2014-03-12 10:02:17',  1),
('7',   '2014-03-12 10:02:24',  0),
('8',   '2014-03-12 11:00:15',  0),
('9',   '2014-03-12 11:01:08',  1),
('10',  '2014-03-12 11:01:17',  0);

I'm trying to get t2_UID COLUMN just like this

ID       CREATED                STABILISATION     t2_UID
'1',    '2014-03-12 09:22:41',  1,                1
'2',    '2014-03-12 09:24:20',  0,                1
'3',    '2014-03-12 09:24:35',  0,                1
'4',    '2014-03-12 09:24:39',  1,                4
'5',    '2014-03-12 09:24:41',  0,                4
'6',    '2014-03-12 10:02:17',  1,                6
'7',    '2014-03-12 10:02:24',  0,                6
'8',    '2014-03-12 11:00:15',  0,                6
'9',    '2014-03-12 11:01:08',  1,                9
'10',   '2014-03-12 11:01:17',  0,                9

whenever the STABILISATION changes the t2_UID should change also!

The closest I get is with this query:

SELECT 
t1.*,
t2.UID AS t2_UID
FROM test AS t1
LEFT JOIN test as t2
ON t2.UID = (SELECT UID
FROM test as t3
WHERE t3.STABILISATION = 1
ORDER BY ABS(TIMEDIFF(t1.CREATED, t3.CREATED))
LIMIT 1)

the closest

Jim · Accepted Answer · 2014-03-12 10:49:16Z

1

This is a quick and dirty way of doing it:

SELECT *,
    (SELECT t2.UID 
    FROM test t2 
    WHERE STABILISATION=1 AND t2.CREATED <= t.CREATED 
    ORDER BY t2.CREATED DESC LIMIT 1) AS t2_id
FROM test t
ORDER BY t.CREATED;

SQL Fiddle

answered Mar 12, 2014 at 10:49

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Strawberry · Accepted Answer · 2014-03-13 15:16:10Z

0

Well done for providing data... the quick and clean way

 SELECT a.uid
      , a.created
      , a.stabilisation
      , b.uid
   FROM 
      ( SELECT x.*
             , MAX(y.created) max_created
          FROM test x 
          JOIN test y
            ON y.created <= x.created 
           AND y.stabilisation = 1
         GROUP 
            BY created
      ) a
   JOIN test b
     ON b.created = a.max_created;
+-----+---------------------+---------------+-----+
| uid | created             | stabilisation | uid |
+-----+---------------------+---------------+-----+
| 1a  | 2014-03-12 09:22:41 |             1 | 1a  |
| 0b  | 2014-03-12 09:24:20 |             0 | 1a  |
| 3c  | 2014-03-12 09:24:35 |             0 | 1a  |
| 0d  | 2014-03-12 09:24:39 |             1 | 0d  |
| 4e  | 2014-03-12 09:24:41 |             0 | 0d  |
| 0f  | 2014-03-12 10:02:17 |             1 | 0f  |
| 5g  | 2014-03-12 10:02:24 |             0 | 0f  |
| 3h  | 2014-03-12 11:00:15 |             0 | 0f  |
| 6i  | 2014-03-12 11:01:08 |             1 | 6i  |
| 7j  | 2014-03-12 11:01:17 |             0 | 6i  |
+-----+---------------------+---------------+-----+

...or something like that

edited Mar 13, 2014 at 15:16

answered Mar 12, 2014 at 11:10

Strawberry

34k14 gold badges43 silver badges57 bronze badges

5 Comments

stash Over a year ago

Thank you! The query looks really simple, but I don't get how the "GROUP BY x.uid" works.

Strawberry Over a year ago

Rewrite it without the aggregation and the GROUP BY, as per the edit.

stash Over a year ago

Your edit code: without any "group" and with 2X "order" makes sql looks almost human :) Thanks.

stash Over a year ago

I found a problem with this query. The MAX(y.UID) happens to function correct due to current UID values, were they actual UUID() it wouldn't function properly. Look at my answer. Jim's answer works in both cases.

Strawberry Over a year ago

Yes, the original solution assumed sequential ids - oops.

Collectives™ on Stack Overflow

MYSQL Nearest row before with another column having a specific value

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related