How to find Previous and Next rows in a table

Sometimes we required to find previous/next record values to perform specific operations in SQL.

For such cases we can use pl/sql LAG function to identify previous row in the table.

Syntax is

LAG ( expression [, offset [, default] ] )
OVER ( [ query_partition_clause ] order_by_clause )

·         expression – The column value for which the data retrieved

·         offset - The number of rows preceding the current row.Default value is 1.

·         default - The value returned if the offset is outside the scope of the window. The default value is NULL.

·         query_partition_clause--It is used to partition the results into groups based on one or more expressions.

·         order_by_clause — It is used to order the data within each partition.

Similarly using LEAD function we can identify next row column value to get the next value from the current record

Syntax is

LEAD ( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause ) 

·         expression – The column value for which the data retrieved

·         offset - The number of rows following the current row.Default value is 1.

·         default - The value returned if the offset is outside the scope of the window. The default value is NULL.

·         query_partition_clause--It is used to partition the results into groups based on one or more expressions.

·         order_by_clause — It is used to order the data within each partition.

For instance,You need to find the duration between order_created and order_delivered date for an Item.

Orders table containing following data

ITEM_NAME	ORDER_DATE	IS_DELIVERED
Headphones XYZ	16-04-2017	N
Headphones XYZ	21-04-2017	Y
Water Bottle	16-04-2017	N
Water Bottle	20-04-2017	Y
Shoes	16-04-2017	N
Shoes	19-04-2017	Y

You can write a query using LAG function to find duration.

SELECT ITEM_NAME,ORDER_INITIATED,ORDER_DEL_DATE,ROUND(DURATION)

FROM (

SELECT ITEM_NAME,LAG(ORDER_DATE,1) OVER (PARTITION BY ITEM_NAME ORDER BY ORDER_DATE) ORDER_INITIATED,ORDER_DATE ORDER_DEL_DATE,

ORDER_DATE-LAG(ORDER_DATE,1) OVER (PARTITION BY ITEM_NAME ORDER BY ORDER_DATE) DURATION,

IS_DELIVERED

FROM ORDERS )

WHERE IS_DELIVERED='Y';

ITEM_NAME	ORDER_INITIATED	ORDER_DEL_DATE	DURATION
Headphones XYZ	16-04-2017	21-04-2017	5
Shoes	16-04-2017	19-04-2017	3
Water Bottle	16-04-2017	20-04-2017	4

LEAD function example:

SELECT ITEM_NAME,ORDER_DATE,LEAD(ORDER_DATE,1) OVER (PARTITION BY ITEM_NAME ORDER BY ORDER_DATE) NEXT_ORDER_DT 

FROM ORDERS;

ITEM_NAME	ORDER_DATE	NEXT_ORDER_DT
Headphones XYZ	21-04-2017	NULL
Shoes	16-04-2017	19-04-2017
Shoes	19-04-2017	NULL
Water Bottle	16-04-2017	20-04-2017
Water Bottle	20-04-2017	NULL