Examples: GROUP BY TIME and TD_SEQNO Column - Advanced SQL Engine - Teradata Database

The USING TIMECODE clause allows you to specify an optional sequence number column. Results are further grouped and ordered by the sequence number column if values in the timecode column are the same. The default system generated sequence number column, TD_SEQNO, is inferred if there is a single sequenced PTI table found. If there are multiple candidates for the TD_SEQNO column to use, then no sequence number is inferred by the database.

The example infers TD_SEQNO as the sequence number column:

select avg(temperature)
from ocean_buoys_seq
group by time(minutes(10));

The next example shows that TD_SEQNO is not inferred as the sequence number column because the USING TIMECODE clause overrides the inference. If a sequence number is not specified in the USING TIMECODE clause that implies a sequence number should not be used for the purpose of grouping:

select avg(temperature)
from ocean_buoys_seq
group by time(minutes(10))
using timecode(td_timecode);

The example shows that TD_SEQNO is not inferred as the sequence number column because there are multiple sequenced PTI tables:

select avg(b.temperature)
from ocean_buoys_seq a , ocean_buoys_seq b
group by time(minutes(100))
using timecode(a.td_timecode);

If your data is sequenced in a non-PTI table, then you can specify a sequence number to use from that table as well. No columns are inferred by the USING TIMECODE clause for non-PTI sources.

The example shows how to specify the sequence number:

select avg(temperature)
from non_pti_tbl_seq
group by time(minutes(10))
using timecode(timecode, sequenceno);