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!
Solved! Go to Solution.
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)
input column1 $;
by column1 notsorted;
if first.column1 then count+ 1;
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)
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 :
sum(flag) over (partition by ManagerName
order by VisitDate
rows unbounded preceding)
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