Better way to select nearest records in Postgresql

Question

Which is best way to select previous and next records from table to allow user to see also nearest products for given product name, e.q Cheese

product table is defined as

create table toode (
  toode char(20) primary key, -- product code
  name char (100)    -- product name
  )

Code below seems to work but looks a bit ugly. Which is best way to implement this in Postgres?

CREATE temp table toodevalik1 on commit drop as 
SELECT * 
FROM toode 
WHERE name  >= 'Cheese'
order by name, toode
limit 50;

CREATE temp table toodevalik2 on commit drop as 
SELECT * 
FROM toode 
WHERE name  < 'Cheese'
order by name desc, toode desc
limit 50;

SELECT * 
FROM toodevalik1
union all
SELECT * 
FROM toodevalik2
order by name, toode;

Erwin Brandstetter · Accepted Answer · 2012-12-19 04:23:22Z

2

Using CTEs might be faster.
Not sure, it depends. Check with EXPLAIN ANALYZE.

WITH x AS (
    SELECT *, row_number() OVER (ORDER BY name, toode) AS rn
    FROM   toode
    )
, y AS (
    SELECT COALESCE(
          (SELECT rn FROM x WHERE fest >= 'Cheese' ORDER BY name, toode LIMIT 1)
         ,(SELECT max(rn) FROM x) -- fallback for value > all
        ) AS rn_mid
    )
SELECT *
FROM   x, y -- cross join ok, 1 row from y if any rows exist.
WHERE  x.rn BETWEEN y.rn_mid - 50 AND y.rn_mid + 50 
ORDER  BY rn;

answered Dec 19, 2012 at 4:23

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Augusto · Accepted Answer · 2012-12-18 16:24:55Z

1

(select name from toode where name > 'Cheese' order by name limit 20)
union all
(select name from toode where name < 'Cheese' order by name desc limit 20)

that query should give the 40 records that surround 'Cheese'

answered Dec 18, 2012 at 16:24

Augusto

8176 silver badges18 bronze badges

1 Comment

Clodoaldo Neto Over a year ago

You need one more order by to sort the result of the union.

Collectives™ on Stack Overflow

Better way to select nearest records in Postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related