How to compare the current row with next and previous row in PostgreSQL?

Question

I want to know how to retrieve results in a SQL query doing some logic comparison with the next or previous rows. I'm using PostgreSQL.

Example
Supposing I have a table in my database with two attributes (ordered position and random numbers), I want to retrieve the odd numbers that are between even numbers. How can I do this?

The real usage
I want to find words that are between two another words which have the category NAME (and the word is not a name). The ordering is provided by sentence and position.

Edit I want to know if the Window function of PostgreSQL are best solution for this kind of problem than doing queries. I heard about them, but never used.

Answer 1

This is my solution using WINDOW functions. I used the lag and lead functions. Both returns a value from a column from a row in offset from the current row. lag goes back and lead goes next in the offset.

SELECT tokcat.text FROM (     SELECT text, category, chartype, lag(category,1) OVER w as previousCategory, lead(category,1) OVER w as nextCategory     FROM token t, textBlockHasToken tb     WHERE tb.tokenId = t.id     WINDOW w AS (         PARTITION BY textBlockId, sentence         ORDER BY textBlockId, sentence, position     ) ) tokcat WHERE 'NAME' = ANY(previousCategory) AND 'NAME' = ANY(nextCategory) AND 'NAME' <> ANY(category) 

---

Simplified version:

SELECT text FROM (     SELECT text           ,category            ,lag(category) OVER w as previous_cat           ,lead(category) OVER w as next_cat     FROM   token t     JOIN   textblockhastoken tb ON tb.tokenid = t.id     WINDOW w AS (PARTITION BY textblockid, sentence ORDER BY position)     ) tokcat WHERE  category <> 'NAME' AND    previous_cat = 'NAME' AND    next_cat = 'NAME'; 

Major points

• = ANY() is not needed, the window function returns a single value
• some redundant fields in the subquery
• no need to order by columns, that you PARTITION BY - the ORDER BY applies within partitions
• Don't use mixed case identifiers without quoting, it only leads to confusion. (Better yet: don't use mixed case identifiers in PostgreSQL ever)

Answer 2

You can find the best solution in this address:

http://blog.sqlauthority.com/2013/09/25/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement-part-4/

Query 1 for SQL Server 2012 and later version:

SELECT LAG(p.FirstName) OVER(ORDER BY p.BusinessEntityID) PreviousValue,     p.FirstName,     LEAD(p.FirstName) OVER(ORDER BY p.BusinessEntityID) NextValue FROM Person.Person p GO 

Query 2 for SQL Server 2005+ and later version:

WITH CTE AS(     SELECT rownum = ROW_NUMBER() OVER(ORDER BY p.BusinessEntityID),     p.FirstName FROM Person.Person p ) SELECT prev.FirstName PreviousValue,     CTE.FirstName,     nex.FirstName NextValue FROM CTE LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1 LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1 GO