All Forums General
VJAIN786 2 posts Joined 09/15
03 Sep 2015
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

 

 

saravanatn 10 posts Joined 07/11
04 Sep 2015

 
 
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

VJAIN786 2 posts Joined 09/15
04 Sep 2015

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

dnoeth 4628 posts Joined 11/04
05 Sep 2015

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.

Dieter

You must sign in to leave a comment.