CODEWITHSUNDEEP
sqlsasmaxteradata
Sylvia
Sylvia

Reputation: 31

How to get max of the rows above in each group(Sql or SAS)

Data:

ID   step order 
100  1    1
100  2    2
100  3    3
100  1    4
200  2    5
200  3    6
200  1    7

Desired Result( I want to get the max of the rows above in each group)

ID   step  max_step
100  1     1
100  2     2
100  3     3
100  1     3
200  2     2
200  3     3
200  1     3

Thank you very much!:)

Upvotes: 1

Views: 455

Answers (3)

dnoeth
dnoeth

Reputation: 60472

You need a Cumulative Max:

max(step)
over(partition by id
     order by ordercol
     rows unbounded preceding)

As Teradata doesn't follow Standard SQL which defaults to range unbounded preceding you need to add it (which it's recommended anyway).

Only last_value defaults to cumulative: 

last_value(step)
over(partition by id
     order by ordercol)

Of course you could add rows unbounded preceding, too.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

If your database supports windowed aggregate then

SELECT id,
       step,
       Max(step) OVER( partition BY ID ORDER BY "order") as max_step
From yourtable

If you want to max step from above rows irrespective of ID then remove the partition by

SELECT id,
       step,
       Max(step) OVER(ORDER BY "order") as max_step
From yourtable

Upvotes: 1

Joe
Joe

Reputation: 63424

If you want to have some idea of row order, then SAS is going to be the easier answer here.

data want;
  set have;
  by ID;
  retain max_step;
  if first.id then call missing(max_step);
  max_step = max(step,max_step);
run;

Upvotes: 0

Related Questions