All geek questions in one place

Next value oracle oracle

How to get the next value (nextname1) for each name1 and id? SQL Oracle. I am trying to use the analytic function LEAD and LAST_VALUE , but it does not work:

 LEAD(name1) OVER (PARTITION BY id ORDER BY id) as nextname1

returns:

 id name1 (nextname1) 1 AA (AA) 2 AA (AA) 3 AA (MB) 4 MB (MB) 5 MB (BB) 6 BB (BB) 7 BB (ZZ) 8 ZZ (null)

I want to:

 id name1 (nextname1) 1 AA (MB) 2 AA (MB) 3 AA (MB) 4 MB (BB) 5 MB (BB) 6 BB (ZZ) 7 BB (ZZ) 8 ZZ (null)

note: I have a table with millions of records.

+4
source share
2 answers

You can do this in two steps:

 SQL> WITH DATA AS ( 2 SELECT 1 id, 'AA' name FROM DUAL 3 UNION ALL SELECT 2 id, 'AA' name FROM DUAL 4 UNION ALL SELECT 3 id, 'AA' name FROM DUAL 5 UNION ALL SELECT 4 id, 'MB' name FROM DUAL 6 UNION ALL SELECT 5 id, 'MB' name FROM DUAL 7 UNION ALL SELECT 6 id, 'BB' name FROM DUAL 8 UNION ALL SELECT 7 id, 'BB' name FROM DUAL 9 UNION ALL SELECT 8 id, 'ZZ' name FROM DUAL 10 ) 11 SELECT v.*, max(first_step) OVER (PARTITION BY name) nextname 12 FROM (SELECT data.*, 13 nullif(lead(name) OVER (ORDER BY id), name) first_step 14 FROM data) v 15 ORDER BY id; ID NA FI NE ---------- -- -- -- 1 AA MB 2 AA MB 3 AA MB MB 4 MB BB 5 MB BB BB 6 BB ZZ 7 BB ZZ ZZ 8 ZZ
+3
source

No need for analytic functions. Use the nested selection:

 with data as ( select 1 id, 'AA' name1 from dual union all select 2 id, 'AA' name1 from dual union all select 3 id, 'AA' name1 from dual union all select 4 id, 'MB' name1 from dual union all select 5 id, 'MB' name1 from dual union all select 6 id, 'BB' name1 from dual union all select 7 id, 'BB' name1 from dual union all select 8 id, 'ZZ' name1 from dual ) select d1.id, d1.name1, ( select name1 from data d2 where d2.id > d1.id and d2.name1 <> d1.name1 and rownum = 1 ) nextname1 from data d1
+1
source

Source: https://habr.com/ru/post/1433822/

More articles:


All Articles