1

I'm looking for some analytic function in Oracle or some query which would find the non-consecutive raw values from table.

Sample Example

ORDER   FLAG    |   ORDER   FLAG
6      TRUE     |    6     TRUE
2      FALSE    |    5     TRUE
2      FALSE    |    4     TRUE
1      FALSE    |    3     TRUE


ORDER   FLAG    |   ORDER   FLAG
2       TRUE    |   7      TRUE
2       TRUE    |   7      TRUE
2       TRUE    |   6      TRUE
1       TRUE    |   1      FALSE
1       TRUE    |   1      FALSE

So, if there's a gap values between two consecutive rows, I'd like to set flag 'False'- and once we set 'False' don't need to check for consecutive values further in that column.

(duplication of values is allowed-so, 2-2-2-1-1 good; but 7-7-6-1-1 fails due to gap between 6 & 1)

Basically I'd like to flag for non-consecutive values from table.

Please let me know is there any analytic function which does this job.

inityk
  • 476
  • 1
  • 9
  • 18

1 Answers1

1

You can use the LAG analytic function which provides access to previous rows, and check if the result is equal to the value of the current row or to the value plus 1.

JeromeFr
  • 1,869
  • 2
  • 17
  • 20
  • could you please provide an example? I checked docs.oracle- but didn't understand properly. – inityk Feb 14 '17 at 11:18
  • Sure, Oracle-Base provides some nice examples here : https://oracle-base.com/articles/misc/lag-lead-analytic-functions – JeromeFr Feb 15 '17 at 07:45