CREATE TABLE MyTable (progressive_no int, ColumnX int);
INSERT INTO MyTable(progressive_no, ColumnX) VALUES
(1,1),
(2,2),
(3,3),
(4,4),
(5,2),
(6,2),
(7,5),
(8,2);
SELECT
t1.ColumnX ,
t1.progressive_no,
MAX(t2.progressive_no) as Previous,
MIN(t3.progressive_no) as Next,
MIN(t4.progressive_no) as First,
MAX(t5.progressive_no) as Last
FROM MyTable t1
LEFT JOIN (
SELECT progressive_no, ColumnX
FROM MyTable t2
ORDER BY t2.progressive_no DESC
) t2 ON t2.ColumnX=t1.ColumnX and t2.progressive_no <t1.progressive_no
LEFT JOIN (
SELECT progressive_no, ColumnX
FROM MyTable t3
ORDER BY t3.progressive_no ASC
) t3 ON t3.ColumnX=t1.ColumnX and t3.progressive_no >t1.progressive_no
LEFT JOIN (
SELECT progressive_no, ColumnX
FROM MyTable t4
ORDER BY t4.progressive_no ASC
) t4 ON t4.ColumnX=t1.ColumnX
LEFT JOIN (
SELECT progressive_no, ColumnX
FROM MyTable t5
ORDER BY t5.progressive_no DESC
) t5 ON t5.ColumnX=t1.ColumnX
WHERE t1.progressive_no=5
GROUP BY t1.progressive_no,t1.Columnx;
output:
ColumnX progressive_no Previous Next First Last
---------- -------------- ---------- ---------- ---------- ----------
2 5 2 6 2 8