Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 '13 at 13:06
    
@dezso Sorry, I edited the post –  alice Nov 24 '13 at 14:24

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
I wonder why you used 3 = ANY(array[id, prev, next]) and not the simpler 3 IN (id, prev, next) –  ypercube Nov 24 '13 at 16:50
1  
And shouldn't there be an ORDER BY inside the OVER (...)? –  ypercube Nov 24 '13 at 16:52
    
Right you are, I missed the ORDER BY in the OVER() clause. I've edited accordingly. Thanks! –  bma Nov 24 '13 at 16:56
    
Thanks! I've edited ORDER BY clause to reflect my original intention. –  alice Nov 24 '13 at 17:31
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. –  Erwin Brandstetter Nov 26 '13 at 16:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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