How to get a specified number of records centered on a specific record in T-SQL
-
Is is possible to write a query that would retrieve say the 20 records that are centered on a specifed record. In other words if I want orderId=9425 then I want the 5 orders for a particular customer that are less then 9425 and the 5 orders after 9425? I can't just say where orderID between 9420 and 9430 as these orders may not belong to this customer. This customers last order may have been orderID 8550 so would not show up in this query.
-
Is is possible to write a query that would retrieve say the 20 records that are centered on a specifed record. In other words if I want orderId=9425 then I want the 5 orders for a particular customer that are less then 9425 and the 5 orders after 9425? I can't just say where orderID between 9420 and 9430 as these orders may not belong to this customer. This customers last order may have been orderID 8550 so would not show up in this query.
Hi, One way of doing this could be:
select *
from orders a
where a.customer = 'A'
and ( a.id in (select top(2) id
from orders b
where b.customer = a.customer
and b.id < 26
order by b.id desc)
or
a.id in (select top(3) id
from orders b
where b.customer = a.customer
and b.id >= 26
order by b.id asc))The above (not tested) should fetch 5 rows. ID 26 and 2 rows from both sides
The need to optimize rises from a bad design.My articles[^]