CODEWITHSUNDEEP
oracle-databaseplsql
Dicky Ho
Dicky Ho

Reputation: 244

Using a single select statment to get the next row from a table or return the first row if the end of table is reached

I have a table say STAFF like below:

STAFF_NAME
============
ALEX
BERNARD
CARL
DOMINIC
EMMA

Now, I want to write a stored function with a single argument. E.g. GET_NEXT_STAFF(CURRENT_STAFF).

The input and output should be like:

Input   |   Output
=====================
NULL    | ALEX
ALEX    | BERNARD
BERNARD | CARL
EMMA    | ALEX (Start from the beginning of the table again)

I know how to handle this problem using PL/SQL, but is it possible to deal with this problem with a single select statement?

Upvotes: 0

Views: 194

Answers (2)

user5683823
user5683823

Reputation:

In the solution below, I assume the rows are ordered alphabetically by names. They may be ordered by another column in the same table (for example by hire date, or by salary, etc. - it doesn't matter) - then the name of that column should be used in the ORDER BY clause of the two analytic functions.

The input name is passed in as a bind variable, :input_staff_name. The solution uses pure SQL, with no need for functions (PL/SQL), but if you must make it into a function, you can adapt it easily.

Edit: In my original answer I missed the required behavior when the input is null. The last line of code (excluding the semicolon) takes care of that. As written currently, the query returns ALEX (or in general the first value in the table) when the input is null, and it returns no rows when the input is not null and not in the table. If instead the requirement is to return the first name when the input is null or not found in the table, then it can be accommodated easily by removing and :input_staff_name is null from the last line.

with
     tbl ( staff_name ) as (
       select 'ALEX'    from dual union all
       select 'BERNARD' from dual union all
       select 'CARL'    from dual union all
       select 'DOMINIC' from dual union all
       select 'EMMA'    from dual
     ),
     prep ( staff_name, next_name, first_name ) as (
       select staff_name, 
              lead(staff_name)         over (order by staff_name),
              first_value (staff_name) over (order by staff_name)
       from   tbl
     )
select nvl(next_name, first_name) as next_staff_name
from   prep
where  staff_name = :input_staff_name 
   or  (next_name is null and :input_staff_name is null)
;

Upvotes: 2

An Van Luu
An Van Luu

Reputation: 1

Based on the answer from @mathguy I have made a few changes that seem to work. I have added the follow

UNION ALL
 SELECT NULL
   FROM DUAL

and

WHERE NVL (staff_name, 'X') = NVL (NULL, 'X');

The full code

WITH tbl (staff_name) AS
    (SELECT 'ALEX' FROM DUAL
     UNION ALL
     SELECT 'BERNARD' FROM DUAL
     UNION ALL
     SELECT 'CARL' FROM DUAL
     UNION ALL
     SELECT 'DOMINIC' FROM DUAL
     UNION ALL
     SELECT 'EMMA' FROM DUAL
     UNION ALL
     SELECT NULL
       FROM DUAL),
 prep (staff_name,
       next_name,
       first_name,
       last_name) AS
    (SELECT staff_name,
            LEAD (staff_name) OVER (ORDER BY staff_name),
            FIRST_VALUE (staff_name) OVER (ORDER BY staff_name),
            LAG (staff_name) OVER (ORDER BY staff_name)
       FROM tbl) 
SELECT NVL (next_name, first_name) AS next_staff_name
  FROM prep
 WHERE NVL (staff_name, 'X') = NVL (:input_staff_name, 'X');

Upvotes: 0

Related Questions