Form pairs from consecutive records for each distinct value of given field in MySQL table-CodePudding

I've got a table with the following data. "record_id" is the autoincremented primary key, "name" is indexed.

record_id name payload
1         a    x
2         b    y
3         c    y
4         b    z
5         a    z
6         a    w
7         b    y
8         c    x

How can I get pairs of consecutive rows for each "name"? I understand that I must join table to itself, but can't figure out how to join only the first of the rows with record_id greater than the one being processed. NULL-filled part after the last row for any "name" is important as well. Again, I understand that I probably must use LEFT JOIN for that.

1 a x 5 a z
5 a z 6 a w
6 a w NULL NULL NULL
2 b y 4 b z
4 b z 7 b y
7 b y NULL NULL NULL
3 c y 8 c x
8 c x NULL NULL NULL

I'm using MySQL pre-8.0, so I can't use window functions if that matters.

Thanks a lot.

CodePudding user response：

Try the following solution that uses an outer join based on the corresponding record_id next-highest value.

select *
from t
left join t t2 on t2.record_id = (
  select record_id from t t2 
  where t2.name=t.name and t2.record_id > t.record_id 
  order by t2.record_id limit 1
) 
order by t.name, t.record_id;

Demo Fiddle

CodePudding user response：

You can use a left join:

select t1.*, t2.* from tbl t1 
left join tbl t2 on t1.name = t2.name and t1.record_id < t2.record_id
where not exists (select 1 from tbl t3 left join tbl t4 on t3.name = t4.name and t3.record_id < t4.record_id where t3.record_id = t1.record_id and t4.record_id < t2.record_id)
order by t1.name

See fiddle