Adding a column with an ascending ID grouped by value

Analytics
Enthusiast

Adding a column with an ascending ID grouped by value

Hi,

I wonder if the following is possible in Teradata. I've been fiddling with this for quite some time applyig all kinds of rank, dense_rank and row_number() over (partition by) functions without success.....

The key thing is that value C below gets 3 and 5 respectively.

Appreciate your help very much!

Rgds

Jack

 

A1
B2
B2
C3
D4
C5
E6
E6
F7
F7
G8
G8
G8
G8

Accepted Solutions
Ambassador

Re: Adding a column with an ascending ID grouped by value

Yes, that's the common solution (if you're on TD16.10+ you can switch to

LAG(DeptName)
over (partition by ManagerName
      order by VisitDate)

instead of the longer MIN(DeptName)..

 

But you still need an ordering column :-)

 

 

1 ACCEPTED SOLUTION
6 REPLIES 6
Ambassador

Re: Adding a column with an ascending ID grouped by value

You need to show how you order your data.

And what are the actual datatypes?

 

Enthusiast

Re: Adding a column with an ascending ID grouped by value

Hi,

I think this is exactly the issue. Teradata will always require columns to be sorted. But I do not want that in this case.
I want the first column in the order that is shown.

I am a SAS developer and I can do this in SAS. There's a 'notsorted' statemen which you can add.
Thing is I want to make use of Teradata to boost performance, as the dataset on which I will do this is huge (hunderds of millions of records).

 

This is the SAS code (I realize this is off topic, but it's just to demonstrate what I'm after)

 

data have;
input column1   $;
datalines;
A
B
B
C
D
C
E
E
F
F
G
G
G
G
;
run;
data want;
  set have;
  by column1 notsorted;
  if first.column1 then count+ 1;
run;

 

Ambassador

Re: Adding a column with an ascending ID grouped by value

There's no order in a table in a Relational DBMS.

Some systems might store the data in the order of inserts, but this can break when you delete/update.

In a parallel DBMS like Teradata data is distributed across many servers/AMPs., how to know which one was first.

And when Teradata would be able to process "notsorted" it would result in a serial/sequential processing like in SAS, i.e. no "performance boost".

 

To be able to process the data in Teradata you need to load it anyway, during load you could add a kind of sequence (e.g. a single session FastLoad into a target table with a GENEATED ALWAYS column)

 

Enthusiast

Re: Adding a column with an ascending ID grouped by value

Hi,

Thanks for the extra information. This is good to be aware of.

I came across a post you solved yourself a while ago, so in the meantime I have the solution :-)

 

This is what I needed :

select dt.*,
   sum(flag) over (partition by ManagerName
                   order by VisitDate
                   rows unbounded preceding)
from
 (
   select
      d.*,
      case when min(DeptName)
                over (partition by ManagerName
                      order by VisitDate
                      rows between 1 preceding and 1 preceding)
              = DeptName then 0 else 1 end as flag
   from have as d
 ) as dt

Ambassador

Re: Adding a column with an ascending ID grouped by value

Yes, that's the common solution (if you're on TD16.10+ you can switch to

LAG(DeptName)
over (partition by ManagerName
      order by VisitDate)

instead of the longer MIN(DeptName)..

 

But you still need an ordering column :-)

 

 

Highlighted
Enthusiast

Re: Adding a column with an ascending ID grouped by value

Thanks for your help. I appreciate !