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.
This is an implementation that does windowing
WITH
data AS (SELECT * FROM id_value),
before AS (
SELECT * FROM id_value
WHERE VALUE < (SELECT VALUE FROM id_value WHERE id = ID)
ORDER BY VALUE DESC, id DESC
limit 1200
),
after AS (
SELECT * FROM id_value
WHERE VALUE >= (SELECT VALUE FROM id_value WHERE id = ID)
ORDER BY VALUE ASC, id ASC
limit 1200
),
windowed AS (
(SELECT * FROM before)
UNION ALL
(SELECT * FROM after)
)
SELECT t.*
FROM (
SELECT *,
COUNT(*) filter (
WHERE id = ID
) over (
ORDER BY VALUE, id rows BETWEEN 5 preceding AND 5 following
) AS cnt
FROM windowed
) t
WHERE cnt > 0;
You could row_number() as follows:
select a, b
from (
select t.*, max(rn) filter(where a = 'bd') over() target_rn
from (
select t.*, row_number() over(order by b, a) rn
from mytable t
) t
) t
where abs(rn - target_rn) <= 2