Identifying the "last" record using the LEAD function

Of course there is no such thing as the "last" record in a relational database. Unless you have an ORDER BY in your query.
For the current project we needed to determine the last record in a set, because this record needs special treatment. One of my colleagues came up with a CASE expression combined with a LEAD function to determine this record.


First let's create a sample table - a simple one with just one column.

SQL> create table test
  2  as
  3  select rownum id
  4    from dual
  5   connect by level <= 5
  6  ;

Table created.

The content of this table are just 5 consecutive numbers, ranging from 1 through 5.

SQL> select id
  2    from test
  3   order by id
  4  ;

        ID
----------
         1
         2
         3
         4
         5

Since we are going to use the LEAD function, it's important to understand what the LEAD function does. With the LEAD function it is possible to access values from others rows in the resultset. The value that is returned is the next one in line, therefore the ORDER BY in the analytic function is important.
Below is the effect shown of the LEAD function. For the first line of the resultset, the value of the second line is shown. For the second line, the third one is shown etcetera. For the last line in the resultset there is no "next line", a NULL is shown.

SQL> select id
  2       , lead (id) over (order by id) ind_last_record
  3    from test
  4   order by id
  5  ;

        ID IND_LAST_RECORD
---------- ---------------
         1               2
         2               3
         3               4
         4               5
         5

Because we want to identify the "last" record, based on the sorted ID, a CASE expression is used where we look at the results of the LEAD:

SQL> select id
  2       , case
  3         when lead (id) over (order by id) is null
  4         then 'Yes'
  5         end ind_last_record
  6    from test
  7   order by id
  8  ;

        ID IND
---------- ---
         1
         2
         3
         4
         5 Yes

And there it is, the last record has the indicator "Yes".

The LEAD function can take up to three arguments. The first argument is the value that you want returned. The second argument is the number of rows that you want to look ahead - the default is 1. The third argument is the value you want to see in case the LEAD function returns NULL - the default is NULL.
Because of the optional arguments we can simplify the query to use "Yes" as the last argument when the LEAD would return NULL.

SQL> select id
  2       , lead (to_char (id), 1, 'Yes') over (order by id)
  3         ind_last_record
  4    from test
  5   order by id
  6  ;

        ID IND_LAST_RECORD
---------- ----------------------------------------
         1 2
         2 3
         3 4
         4 5
         5 Yes

For the first argument a TO_CHAR is required, otherwise the exception ORA-1722 (Invalid Number) is raised - After all, "Yes" is not a NUMBER.
Last step to take: only the indicator for the last record is required, let's change the first argument to NULL instead of TO_CHAR (id)

SQL> select id
  2       , lead (null, 1, 'Yes') over (order by id)
  3         ind_last_record
  4    from test
  5   order by id
  6  ;

        ID IND
---------- ---
         1
         2
         3
         4
         5 Yes

Pretty nifty trick, if I say so myself.... :)
... and don't forget to cleanup the testset.

SQL> drop table test purge
  2  ;

Table dropped.

Oracle documentation
Lead Analytic Function