PostgreSQL: select nearest rows according to sort order

Question

I have a table like this:

     a    |  user_id
----------+-------------
  0.1133  |  2312882332
  4.3293  |  7876123213
  3.1133  |  2312332332
  1.3293  |  7876543213
  0.0033  |  2312222332
  5.3293  |  5344343213
  3.2133  |  4122331112
  2.3293  |  9999942333

And I want to locate a particular row - 1.3293 | 7876543213 for example - and select the nearest 4 rows. 2 above, 2 below if possible.
Sort order is ORDER BY a ASC.

In this case I will get:

  0.0033  |  2312222332
  0.1133  |  2312882332
  2.3293  |  9999942333
  3.1133  |  2312332332

How can I achieve this using PostgreSQL? (BTW, I'm using PHP.)

P.S.: For the last or first row the nearest rows would be 4 above or 4 below.

Answer 1

Test case:

CREATE TEMP TABLE tbl(a float, user_id bigint);
INSERT INTO tbl VALUES
 (0.1133, 2312882332)
,(4.3293, 7876123213)
,(3.1133, 2312332332)
,(1.3293, 7876543213)
,(0.0033, 2312222332)
,(5.3293, 5344343213)
,(3.2133, 4122331112)
,(2.3293, 9999942333);

Query:

WITH x AS (
    SELECT a
          ,user_id
          ,row_number() OVER (ORDER BY a, user_id) AS rn
    FROM   tbl
    ), y AS (
    SELECT rn, LEAST(rn - 3, (SELECT max(rn) - 5 FROM x)) AS min_rn
    FROM   x
    WHERE  (a, user_id) = (1.3293, 7876543213)
    )
SELECT *
FROM   x, y
WHERE  x.rn  > y.min_rn
AND    x.rn <> y.rn
ORDER  BY x.a, x.user_id
LIMIT  4;

Returns result as depicted in the question. Assuming that (a, user_id) is unique.

It is not clear whether a is supposed to unique. That's why I sort by user_id additionally to break ties. That's also why I use the window function row_number(), an not rank() for this. row_number() is the correct tool in any case. We want 4 rows. rank() would give an undefined number of rows if there were peers in the sort order.

This always returns 4 rows as long as there are at least 5 rows in the table. Close to first / last row, the first / last 4 rows are returned. The two rows before / after in all other cases. The criteria row itself is excluded.

---

Improved performance

This is an improved version of what @Tim Landscheidt posted. Vote for his answer if you like the idea with the index. Don't bother with small tables. But will boost performance for big tables - provided you have a fitting index in place. Best choice would be a multicolumn index on (a, user_id).

WITH params(_a, _user_id) AS (SELECT 5.3293, 5344343213) -- enter params once
    ,x AS  (
    (
    SELECT a
          ,user_id
          ,row_number() OVER (ORDER BY a DESC, user_id DESC) AS rn
    FROM   tbl, params p
    WHERE  a < p._a
       OR  a = p._a AND user_id < p._user_id -- a is not defined unique
    ORDER  BY a DESC, user_id DESC
    LIMIT  5  -- 4 + 1: including central row
    )
    UNION ALL -- UNION right away, trim one query level
    (
    SELECT a
          ,user_id
          ,row_number() OVER (ORDER BY a ASC, user_id ASC) AS rn
    FROM   tbl, params p
    WHERE  a > p._a
       OR  a = p._a AND user_id > p._user_id
    ORDER  BY a ASC, user_id ASC
    LIMIT  5
    )
    )
    , y AS (
    SELECT a, user_id
    FROM   x, params p
    WHERE (a, user_id) <> (p._a, p._user_id) -- exclude central row
    ORDER  BY rn  -- no need to ORDER BY a
    LIMIT  4
    )
SELECT *
FROM   y
ORDER  BY a, user_id   -- ORDER result as requested

Major differences to @Tim's version:

• According to the question (a, user_id) form the search criteria, not just a. That changes window frame, ORDER BY and WHERE clause in subtly different ways.

• UNION right away, no need for an extra query level. You need parenthesis around the two UNION-queries to allow for individual ORDER BY.

• Sort result as requested. Requires another query level (at hardly any cost).

• As parameters are used in multiple places I centralized the input in a leading CTE.
  For repeated use you can wrap this query almost 'as is' into an SQL or plpgsql function.