25 February 2010

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

6 comments:

  1. Wonderful post! This is very useful to many readers like me. Being a student, I am requiring myself
    to read articles more often and your writing just caught my interest. Thank you so much!

    ReplyDelete
  2. I did not know arguments of lead function and was using "case when .... is null" to identify the last row. This article help simplify my code a lot. Thank you!

    ReplyDelete
  3. Thanks for this post. It was useful to identify and discard the last row of my query.

    ReplyDelete
  4. There is also another simple way to make use of inbuilt method last(). Here is the solution

    First you need to make the result set scrollable as well as updatable. Updatable means, you directly insert into result set. Then you can use the .last() method, or .absolute(int row) method to move through the result set. Make the following changes.

    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

    You are done. Use the last()method, it won't throw any exception, Your code will work like charm

    ReplyDelete
    Replies
    1. This blogpost is about pure Oracle SQL, not about Java (I assume that's what your code snippet is).. Keep data related functionality as close to the data as you can - that's what I believe...

      Delete
  5. Tx for the post Alex.
    Of course you know about row_number. Do you have any performance indicators that compare different ways to determine "last record" ?

    ReplyDelete