The following returns the same results and is about twice as fast even with only 8 records in MyTable ... and it should always take about the same time no matter how many records are in the table.
```
CREATE TABLE MyTable
(
progressive_no integer primary key,
ColumnX text
);
create index idx on MyTable(ColumnX);
INSERT INTO MyTable (ColumnX) VALUES
(1),
(2),
(3),
(4),
(2),
(2),
(5),
(2);
select * from MyTable;
progressive_no ColumnX
-------------- ----------
1 1
2 2
3 3
4 4
5 2
6 2
7 5
8 2
SELECT t1.ColumnX,
t1.progressive_no,
(
select progressive_no
from MyTable
where ColumnX == t1.ColumnX
and progressive_no < t1.progressive_no
order by progressive_no desc
limit 1
) as previous,
(
select progressive_no
from MyTable
where ColumnX == t1.ColumnX
and progressive_no > t1.progressive_no
order by progressive_no
limit 1
) as next,
(
select progressive_no
from MyTable
where ColumnX == t1.ColumnX
order by progressive_no
limit 1
) as first,
(
select progressive_no
from MyTable
where ColumnX == t1.ColumnX
order by progressive_no desc
limit 1
) as last
from MyTable as t1
where progressive_no == 5;
ColumnX progressive_no previous next first last
---------- -------------- ---------- ---------- ---------- ----------
2 5 2 6 2 8
```