Return X elements before and after sorted by a property that is not unique SQL

Question

I am trying to write a query that returns X elements before and after a given entity sorted by a property that is not unique.

For example: Property a is the primary column (a unique UUID), b is the property I would like to sort by

  table
  --------
  a      b
  --------
  ag     1     
  sb     1
  sf     1
  xk     2
- bd     2
  ve     2
  ku     2
  lt     3
  ac     3

If I wanted to return the elements before and after a = bd sorted by b

Before

SELECT * FROM table WHERE b >= 2 ORDER BY b DESC, a DESC LIMIT x

After

SELECT * FROM table WHERE b <= 2 ORDER BY b ASC, a DESC OFFSET 1 LIMIT x

If the property of b was unique this would work. How would I do this on a non-unique property.

Answer 1

You can use window functions:

select t.*
from (select t.*,
             count(*) filter (where a = 'bd') over (order by b, a rows between x preceding and x following) as cnt
      from t
     ) t
where cnt > 0;

Here is a db<>fiddle.