Peek into the past and the future with LAG and LEAD
by maggie on Sep.21, 2007, under entry
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.
-
-->
Recently Linked From- Kühle URLs November 19, 2009Nochmal alle zusammen, und drei, vier: "Cool URLs don't change!" (genauer: Cool URIs don't change), wie schon der Erfinder des Internet, Al Gore Tim Berners-Lee forderte, der damit erreichen will, dass Inhalte immer im Netz auffindbar ... […]Konstantin Klein
- ORM в мире PHP October 21, 2009ORM (англ. Object-relational mapping, русск. Объектно-реляционная проекция) — запись объектов программы в реляционную базу данных, отображение объекта и его представления в виде набора таблиц. – Википедия: ORM ... […]ingvar
- “Micro” Optimizations That Matter October 16, 2009Every few weeks, someone publishes an article talking about how it's faster to use single quotes rather than double quotes and how you should use echo() instead of print(). Most of these are bunk; that is, the time we spend talking ... […]Brandon Savage
- Tour du mois en PHP : mars 2009 April 20, 2009Comme tous les mois, en voici un nouveau qui s'est achevé — avec sa quantité d'articles et d'événements intéressants ! Voici ma compilation de liens m'ayant le plus marqués pour le mois de mars 2009. ... […]Pascal MARTIN
- stop sacrificing readability for efficiency! March 26, 2009much was made last week over the topic of micro optimization in php. most of these argued that micro optimization was a bad idea, from the perspective of unit testing, the idea that “premature optimization is the root of all evil”, ... […]Brandon Savage
- Kühle URLs November 19, 2009
September 21st, 2007 on 8:52 pm
So… I guess this means I’ll get those previous and next links after all? ;-)
September 22nd, 2007 on 12:03 am
Yes, but only because there is a cool Oracle function to do it. :)