Key change column

[vamsi.4a6]

THE SOURCE IS FROM ORACLE

ID NAME st_DATE
-- -- ---

1 MA 10-FEB-2010
1 MA 11-FEB-2010
1 MA 12-FEB-2010
2 CA 1-MAR-2010
2 CA 2-MAR-2010
2 CA 3-MAR-2010
3 NE 4-APR-2010
3 NE 5-APR-2010


O/P REQUIRED


ID NAME st_DATE KEYCHANGE
-- -- --- -----

1 MA 12-FEB-2010 1
1 MA 11-FEB-2010 0
1 MA 10-FEB-2010 0
2 CA 3-MAR-2010 1
2 CA 2-MAR-2010 0
2 CA 1-MAR-2010 0
3 NE 5-APR-2010 1
3 NE 4-APR-2010 0

My key column is ID and NAME.My requirement is for a particular key which is having maximum st_DATE i need to assign one and remaining records i need to assign
0 for that id .


I have done like this sort stage with link sort based on ID and NAME with both sorting and partition and in sort stage i used key column as st_DATE with desc with
sort key mode as-sort with create Key change column=true but i did not get required output please correct me?

[qt_ky]

Add a final Sort stage with the key change column based on sort key = ID. If your key change column is based on sort key = st_DATE then you'll get a lot of 1's and not a lot of 0's, for every time st_DATE changes values.

[vamsi.4a6]

Is it possible to achieve this with one sort stage with the following options?

link sort based on ID and NAME with both sorting and partition
Descending on st_DATE

In sort stage key column is ID and NAME with do not sort previously sorted with create key change column=true

[TPons]

If NAME does not vary across records for a single ID value, your approach should work fine..

But dont forget to set Ascending order sort for ID and NAME columns and Descending order sort for st_DATE column

[qt_ky]

Try it out and let us know what you find.