For simplicity, let's say I have a table like this.

| id    |  a     |   b  |  c   |
| 1     | 100    |  b1  | c1   |
| 2     | 104    |  b2  | c2   |
| 3     | 106    |  b1  | c1   |
| 5     | 118    |  b2  | c2   |

I want to find next record which field b and c share the same value as previous one, and field a is greater than previous one. There may be more than one records match that, but I only want to get the first one which matches it. I do not know how to write such a SQL to get result like below. i.e.:

| id    |  a     |   b    |  c   | matched_id. | b(1) | c(1) |
| 1     | 100    |  b1    | c1   |   3         | b1   |  c1  |
| 2     | 104    |  b2    | c2   |   5         | b2   |  c2  |
| 3     | 106    |  b1    | c1   |   null      | null |  null|
| 5     | 118    |  b2    | c2   |   null      | null |  null|

BTW, I am using MySQL 5.7.

CodePudding user response：

This solution uses a self join and ROW_NUMBER to get only the first, whuich will work only in MySQL 8.

To show that it works i added another row with id 6

CREATE TABLE tab1 (
  `id` INTEGER,
  `a` INTEGER,
  `b` VARCHAR(4),
  `c` VARCHAR(4)
);

INSERT INTO tab1
  (`id`, `a`, `b`, `c`)
VALUES
  ('1', '100', 'b1', 'c1'),
  ('2', '104', 'b2', 'c2'),
  ('3', '106', 'b1', 'c1'),
  ('5', '118', 'b2', 'c2'),
   ('6', '120', 'b1', 'c1');

SELECT 
`id`, `a`, `b`, `c`,`id(1)`, `b(1)`, `c(1)`
FROM
(SELECT t1.id,t1.a,t1.b,t1.c
,t2.id `id(1)`
,t2.b as `b(1)`
,t2.c `c(1)`
, ROW_NUMBER() OVER(PARTITION BY t1.id ORDER BY t1.id,t2.id) rn
FROM tab1 t1 LEFT JOIN tab1 t2 ON t1.b = t2.b AND t1.c = t2.c and t1.id<t2.id) t3
WHERE rn = 1

id |   a | b  | c  | id(1) | b(1) | c(1)
-: | --: | :- | :- | ----: | :--- | :---
 1 | 100 | b1 | c1 |     3 | b1   | c1  
 2 | 104 | b2 | c2 |     5 | b2   | c2  
 3 | 106 | b1 | c1 |     6 | b1   | c1  
 5 | 118 | b2 | c2 |  null | null | null
 6 | 120 | b1 | c1 |  null | null | null

db<>fiddle here

MySQL 5.7 version

SELECT 
`id`, `a`, `b`, `c`,`id(1)`, `b(1)`, `c(1)`
FROM
(SELECT 
`id`, `a`, `b`, `c`,`id(1)`, `b(1)`, `c(1)`
,if (@t1id = id, @rn := @rn 1, @rn := 1) rn
, @t1id := id
FROM
(SELECT t1.id,t1.a,t1.b,t1.c
,t2.id `id(1)`
,t2.b as `b(1)`
,t2.c `c(1)`
FROM tab1 t1 LEFT JOIN tab1 t2 ON t1.b = t2.b AND t1.c = t2.c and t1.id<t2.id
ORDER BY t1.id,t2.id
LIMIT 100000) t5
,(SELECT @t1id := 0, @rn := 0) t4) t6
WHERE rn = 1
ORDER BY id

id |   a | b  | c  | id(1) | b(1) | c(1)
-: | --: | :- | :- | ----: | :--- | :---
 1 | 100 | b1 | c1 |     3 | b1   | c1  
 2 | 104 | b2 | c2 |     5 | b2   | c2  
 3 | 106 | b1 | c1 |     6 | b1   | c1  
 5 | 118 | b2 | c2 |  null | null | null
 6 | 120 | b1 | c1 |  null | null | null

db<>fiddle here

CodePudding user response：

You can use LATERAL, that makes things easy IMHO:

select t1.*, tt.id matched_id, tt.b b1, tt.c c1
 from myTable t1
 left outer join lateral 
 (select * from myTable t2
    where t1.b = t2.b and t1.c = t2.c and t2.a > t1.a
    limit 1
 ) tt on true

Here is DBFiddle demo.

EDIT: I didn't notice it was 5.7 before, sorry. Here is an 5.7 version:

select tmp.*, t3.b b1, t3.c c1
from (select id, b, c, 
  (select min(id) 
   from myTable t2 
   where t1.b = t2.b and t1.c = t2.c and t2.a > t1.a) as matched_id
 from myTable t1) tmp
 left join myTable t3 on t3.id = tmp.matched_id;

CodePudding user response：

This one worked with me:

SELECT p.id, p.a, p.b, p.c, matched_id.id,matched_id.b, matched_id.c 
FROM test p left join test matched_id on  
    matched_id.a = (select min(a) 
                    from test m 
                    where m.a>p.a and m.b=p.b and m.c = p.c);

There were some trial and error on this, so it's not so clean, sorry.

db-fiddle: https://www.db-fiddle.com/f/6w4NsFzCi6Qm4sfUgkKLgf/0