How can I select adjacent rows to an arbitrary row (in sql or postgresql)?

Question

I want to select some rows based on certain criteria, and then take one entry from that set and the 5 rows before it and after it.

Now, I can do this numerically if there is a primary key on the table, (e.g. primary keys that are numerically 5 less than the target row's key and 5 more than the target row's key).

So select the row with the primary key of 7 and the nearby rows:

select primary_key from table where primary_key > (7-5) order by primary_key limit 11;  2 3 4 5 6 -=7=- 8 9 10 11 12 

But if I select only certain rows to begin with, I lose that numeric method of using primary keys (and that was assuming the keys didn't have any gaps in their order anyway), and need another way to get the closest rows before and after a certain targeted row.

The primary key output of such a select might look more random and thus less succeptable to mathematical locating (since some results would be filtered, out, e.g. with a where active=1):

select primary_key from table where primary_key > (34-5)      order by primary_key where active=1 limit 11;  30 -=34=- 80 83 100 113 125 126 127 128 129 

Note how due to the gaps in the primary keys caused by the example where condition (for example becaseu there are many inactive items), I'm no longer getting the closest 5 above and 5 below, instead I'm getting the closest 1 below and the closest 9 above, instead.

Answer 1

There's a lot of ways to do it if you run two queries with a programming language, but here's one way to do it in one SQL query:

(SELECT * FROM table WHERE id >= 34 AND active = 1 ORDER BY id ASC LIMIT 6) UNION (SELECT * FROM table WHERE id < 34 AND active = 1 ORDER BY id DESC LIMIT 5) ORDER BY id ASC 

This would return the 5 rows above, the target row, and 5 rows below.