cancel
Showing results for 
Search instead for 
Did you mean: 

how to pass last record that have few input records

Former Member
0 Kudos


Hi All,

i have table with 2 rows that contain 3 columns.

c1        c2                   c3

x         01/01/2010        10

x         01/01/2012         5

the 2 records have the same value in column c1

i need that the output will take only  one reocrd for each value from c1 ( in my example x )

the record with the max value and the value of c3 of the same max date value from column c2

so in my example in need that the output file will take only the second recrod.

c1             c2                         c3

x             01/01/2012               5

regards,

Ilan

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi llan,

You can use previous_row value function. Use if previous_row > current_row or vice versa then 1 else 0. Select 1s and discard 0s.

Another method is first sort by c3; use gen_row_num_by_group() combining c2 and c3 to generate row count. In the next query select the record with max row count value of the generated gen_row_num_group().

Arun

Former Member
0 Kudos

Hi Arun,

thank you very much  for the information.

i used the option with previous_row function and it's work correct

regards,

Ilan haik

Former Member
0 Kudos

Hi Jonathan,

If you think my answer helped you, please provide appropriate credit.

Thanks,
Arun

Answers (0)