Well, I guess it can be done. Is this a real world problem? Nevertheless, I was a bit curious too, so this is what I came up with (given your input data):
select temp.x, monotonic() as m, calculated m +1 - rowNo as y
from temp inner join
(select x, min(monotonic()) as rowNo
group by x) as r
on temp.x eq r.x;
One strange thing is that I had to put the result of the monotonic function in a column before using it in a calculation, otherwise, I got totally different result. Maybe some SAS person can explain that (the monotonic was just until recently unsupported and still pretty undocumented).
But the baseline is that the SQL code isn't especially pretty, and if you are using SAS, the data step will probably a better choice for this problem.
Thank you Linus ,
I appreciate the time you spend on it !
In sas code its realy not a problem but I was curious if it can be done with sql.
I tried to think about a way to solve it and nothing cross my mind.
Thanks again for your help!!!