CODEWITHSUNDEEP
sqlpostgresql
Andrus
Andrus

Reputation: 27931

Better way to select nearest records in Postgresql

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;

Upvotes: 3

Views: 173

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656814

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;

Upvotes: 2

Augusto
Augusto

Reputation: 819

(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'

Upvotes: 1

Related Questions