I have the following table:

  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?

  • 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?
    – dezso
    Nov 24, 2013 at 13:06
  • @dezso Sorry, I edited the post
    – alice
    Nov 24, 2013 at 14:24

1 Answer 1


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

  • 1
    And shouldn't there be an ORDER BY inside the OVER (...)? 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
    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. 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. 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
    Nov 13, 2017 at 9:52

Your Answer

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

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