Matching results to the very next sequence in the same table-CodePudding

I have a table with a Job #, a Job Suffix, and a Job Sequence. I am trying to match a table to itself and pull in the very next sequence so if it is job 00001, suffix 001, and sequence 100, I want to join those results to the next sequence which for this example could be 200, but is not always, and display the results as 00001, 001, 100, 200. I have gotten the matches to find all higher sequences, but cannot narrow it down to just select the lowest of the matches

select a.job, a.sfx, a.seq, b.seq, from a 
left join b 
on a.job=b.job and a.sfx=b.sfx 
where a.seq<b.seq

Example of table:

|  Job  | Sfx | Seq |
| 00001 | 001 | 100 | 
| 00001 | 001 | 200 | 
| 00001 | 001 | 300 |

Results of current query:

|  Job  | Sfx | Seq  | b.seq |
| 00001 | 001 |  100 |   200 | 
| 00001 | 001 |  100 |   300 |

It is okay for the results to repeat for each subsequent sequence, e.x. seq 200 has a row matched to 300, and 300 is left unmatched to any row, but for sequences with more than 1 match I only want to see the min sequence it matches to, please let me know if that is unclear.

Thank you

CodePudding user response：

Since you can't use the LEAD function, you may try the following:

SELECT A.Job, A.Sfx, A.Seq, MIN(B.Seq) B_seq 
FROM table_name A LEFT JOIN table_name B
ON A.Job = B.Job
AND A.Seq < B.Seq
GROUP BY A.Job, A.Sfx, A.Seq
ORDER BY A.Job, A.Sfx, A.Seq

See a demo.