CODEWITHSUNDEEP
sqloracle-databaseanalytic-functions
inityk
inityk

Reputation: 486

Find non-consecutive raw values from DB table- SQL

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.

Upvotes: 1

Views: 111

Answers (1)

JeromeFr
JeromeFr

Reputation: 1928

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.

Upvotes: 1

Related Questions