CODEWITHSUNDEEP
sqloracle-databaseplsql
Victor
Victor

Reputation: 1271

Get next row value based on returned list of rows

In Oracle, suppose I have a query that returns the following list:

ID     Sequence#
12        1
15        3
25        5

All I know in this case is the ID of some row (let's suppose 12), I need to return the ID of a row with the next sequence number which in this case is 3 (id = 15)

How can I do it? I know there's a Oracle function lead, but I wasn't able to successfully impement is.

Upvotes: 2

Views: 2792

Answers (3)

Ahmad
Ahmad

Reputation: 12717

Select sequence from my_ table where id=(select min(id) from my_table where sequence> 1)

Replace (1) in the above query with any value that you are searching for its next

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27251

Yes, you can use lead function to get the next value. Here is an example of how it can be done.

-- sample of data from your question
SQL> with t1(ID, Sequence#) as
  2  (
  3    select 12, 1 from dual union all
  4    select 15, 3 from dual union all
  5    select 25, 5 from dual
  6  )
  7  select *
  8    from (select id
  9               , sequence#
 10               , lead(sequence#) over(order by id) next_sequence#
 11               , lead(id) over(order by id) next_id#
 12           from t1
 13         )
 14   where id = 12
 15  ;

        ID  SEQUENCE# NEXT_SEQUENCE#   NEXT_ID#
---------- ---------- -------------- ----------
        12          1              3         15

Upvotes: 3

user3378
user3378

Reputation: 

SELECT * FROM table1 where ID in (SELECT min(ID) FROM table1 WHERE ID > 12)

Upvotes: 1

Related Questions