17

I have the following table:

CREATE TABLE post (
  id            bigint primary key,
  thread_id     bigint,
  is_notice     boolean,
  title         text,
  content       text
)

I display the list using the following query:

SELECT * FROM post ORDER BY is_notice desc, thread_id desc, id

Then, given the post selected by id(i.e. SELECT * FROM post where id=3), how do I retrieve the next and previous posts?

2
  • 1
    Well, not having a WHERE clause, you return all rows from the table, there is no next or previous. Could you explain a bit better? Commented Nov 24, 2013 at 13:06
  • @dezso Sorry, I edited the post
    – alice
    Commented Nov 24, 2013 at 14:24

1 Answer 1

29

Using PostgreSQL's Window Functions, specifically LAG and LEAD, should be able to show you the previous and next entries in your table.

select *
from (
    select  id, thread_id, is_notice, title, content,
            lag(id) over (order by is_notice desc, thread_id desc, id asc) as prev,
            lead(id) over (order by is_notice desc, thread_id desc, id asc) as next
    from post
    ) x
where 3 IN (id, prev, next);

A demo can be found here: http://sqlfiddle.com/#!15/9fd7a/8

8
  • 1
    And shouldn't there be an ORDER BY inside the OVER (...)? Commented Nov 24, 2013 at 16:52
  • 1
    Right you are, I missed the ORDER BY in the OVER() clause. I've edited accordingly. Thanks!
    – bma
    Commented Nov 24, 2013 at 16:56
  • 1
    @ypercube: You may be interested that 3 IN (id, prev, next) is rewritten to 3 = ANY('{id, prev, next}') internally. EXPLAIN ANALYZE reveals it. Commented Nov 26, 2013 at 0:52
  • 1
    @bma: Well, ultimately either of these expressions is resolved to a list of ORed expressions. Maybe, the 9.3.1 planner goes one step further in this process. I didn't retest with the latest version. Commented Nov 26, 2013 at 16:48
  • 2
    Wow the 3 in (id, prev, next) blows my mind, I wish I knew about that before :)
    – Davos
    Commented Nov 13, 2017 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.