Peek into the past and the future with LAG and LEAD

2007-09-21

Traditionally (or at least wherever I have seen it), doing queries that involve getting one row of data that combines data from many rows is kind of complicated. Probably as complicated as it sounds. The approaches usually end up in cartesian joins, complicated subqueries, and plenty of joins on the same table. An example situation is when you want to get a specific blog post for your blog along with some minimal information about a previous and a next post. In this case, you want just one row, with the data about the current blog post and just another field or two for the titles of the previous and next post. Here's how Oracle's LAG and LEAD functions can help you out. Let's say your database table for blogs is something like this:

BLOG_ENTRY
-----------------
id (PK)
title
body
created_date

Here's the SQL that returns the id and title of the current blog post, along with the id of the previous and the next one:

SELECT *
FROM (SELECT id,
LEAD(id, 1) OVER (ORDER BY created_date DESC) AS prev_id,
LAG(id, 1) OVER (ORDER BY created_date DESC) AS next_id
FROM blog_entry)
WHERE id = :id;

Ta-da! Two things worthy of notice:

• 1. The 2nd parameter to LEAD and LAG is the offset from the current row. It is not calculating the offset based on the id (which just happens to be numeric), but the number of the row - very similar to the ROWNUM function.
• 2. LEAD and LAG can take an optional 3rd parameter, which will specify a default value to return. If you don't specify a default, these functions will return NULL. If you wish, you can leverage this in your application to not show a "Previous" or "Next" link if that value is NULL.