How to find CONSECUTIVE values coming in a column(TERADATA)

General

How to find CONSECUTIVE values coming in a column(TERADATA)

HI,

I have a table A having 1 column which is having integer values( Consecutive & Non-Consecutive) like below:

column1

7

4

2

3

5

6

9

10

11

Now i need to write a query which shows YES for consecutive values and NO for Non-Consecutive values like below:


column1     column2

7                  NO

4                  NO

2                  NO

3                  YES

5                  NO

6                  YES

9                  NO

10                YES

11                YES


3 REPLIES
Enthusiast

Re: How to find CONSECUTIVE values coming in a column(TERADATA)

Hi,

I think below query will give the result you are looking for:

select employee_id,case when tr=1 then 'yes' else 'no' end as sequence

from

(select  employee_id,(employee_id-coalesce(max(employee_id)over(order by employee_id rows between  1 preceding and 1 preceding),0)) as tr

 from storeemp) as seq1

Re: How to find CONSECUTIVE values coming in a column(TERADATA)

Hi Saravanatn,

Thanks for reply!!

I tried your query, the original sequence of rows is changed (ascending order) but in expected result we dont want the original sequence to be changed.

Original Table:              Expected Result:                                     

column1                       column1     column2                              

7                                    7               NO

4                                    4               NO

2                                    2               NO

3                                    3               YES

5                                    5               NO

6                                    6               YES

9                                    9               NO

10                                  10             YES

11                                  11             YES

Senior Apprentice

Re: How to find CONSECUTIVE values coming in a column(TERADATA)

There's no "original sequence" in a table in a relational DBMS.

You need to add an ORDER BY to get a reliable & repeatable result, if there's no ordering column you're out of luck.