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

by

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)

More Related Contents:

  1. Select rows which are not present in other table
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Aggregate columns with additional (distinct) filters
  5. Computed / calculated / virtual / derived columns in PostgreSQL
  6. Check if value exists in Postgres array
  7. Insert data in 3 tables at a time using Postgres
  8. What is the difference between single quotes and double quotes in PostgreSQL?
  9. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  10. PostgreSQL: Which Datatype should be used for Currency?
  11. Best way to count rows by arbitrary time intervals
  12. PostgreSQL sequence based on another column
  13. Sorting null values after all others, except special
  14. Finding similar strings with PostgreSQL quickly
  15. Reference alias in WHERE clause
  16. Sort NULL values to the end of a table
  17. Return setof record (virtual table) from function
  18. Alphanumeric sorting with PostgreSQL
  19. Transposing an sql result so that one column goes onto multiple columns
  20. SQL column reference “id” is ambiguous
  21. How to convert PostgreSQL 9.4’s jsonb type to float
  22. postgres – where in (list) – column does not exist
  23. incomplete information from query on pg_views [duplicate]
  24. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  25. How to get a list column names and datatypes of a table in PostgreSQL?
  26. Referencing current row in FILTER clause of window function
  27. Postgresql query between date ranges
  28. How to use SQL LIKE condition with multiple values in PostgreSQL?
  29. Why isn’t row level security enabled for Postgres views?
  30. Is there a postgres CLOSEST operator?

Leave a Comment