Search⌘ K
Home/
Courses/
Practical Data Analysis with SQL

Next and Previous Rows

Learn how to get values from next or previous rows in SQL.

We'll cover the following...

Overview

In many types of analysis, it is often necessary to compare a row to the next or previous rows. For example, calculating the change in a stock price, calculating the increase or decrease of a certain measure, and so on. Window functions are a great fit for this type of analysis.

Given our temperature data set, say we want to find the temperature change on each day. To do that, for each day, we need to find the temperature of the previous day:

PostgreSQL
SELECT
  t_outer.*,
  (
    -- Find the last temperature in the same city
    SELECT 
      temperature
    FROM
      temperatures t_inner  
    WHERE
      -- Same city
      t_inner.city = t_outer.city
      -- Days prior to "outer" current date
      AND t_inner.day < t_outer.day
    ORDER BY
      t_inner.day DESC
    LIMIT 
      1
  ) previous_temperature
FROM
  temperatures t_outer
ORDER BY
  t_outer.city,
  t_outer.day;

The query uses a subquery to find the previous temperature in each city on each day. The subquery finds all the temperatures in the same city in days prior to the current row in the outer query. It then sorts the results by the date in descending order and takes the first result. This is how we ...