The LAG (to access data from a previous row) and LEAD (to return data from the next row) analytic functions were introduced to give access to multiple rows within a table, without the need for a self-join.

Both LAG and LEAD functions have the same usage:

LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

– Value_expression: can be a column or a built-in function, except for other analytic functions.
– Offset: the number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
– Default: the value returned if the offset is outside the scope of the window. The default value is NULL.

SELECT cd_gruppo,
       LEAD(cd_voce, 1, -1) OVER(ORDER BY cd_voce) AS "NextHired",
       LAG(cd_voce, 1, -1) OVER(ORDER BY cd_voce) AS "LastHired"
  FROM menu_voci
 WHERE cd_gruppo = 98
   and cd_capitolo = 1;