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

user_8762885 0
Viewed 70

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.

sql-limit window-functions sql-order-by postgresql sql
4 Answers

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.

user_11440350

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;
user_62584590

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
user_106767160

If the count of rows forward and backward is not a fixed number you might find something like this to be useful.

 with
    data as (select * from t /* or filter results here if complicated */),
    bck as (
        select *, dense_rank() over (order by b desc) grp
        from data where b < (select b from t where a = 'bd')
        -- where /* try filtering here first */
        order by b desc
        limit 100
    ),
    fwd as (
        select *, dense_rank() over (order by b) grp
        from data where b >= (select b from t where a = 'bd')
        -- where /* try filtering here first */
        order by b
        limit 100
    ) 
select * from bck where grp  = 1
union all
select * from fwd where grp <= 2
order by b;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a5318a7872bf5e7dc347a00ea4b9f3fc

user_13222680
Related