How do I fetch the rows which have the Max value in a different column

[Former Member]

I have to update a column in a very, very large table. So performance is a big issue.

The question is the same as this one, but only for Sybase. Is there a way to do it without a self join like in the top voted answer for Oracle?

This was my first attempt, but it is by orders to slow for the table it is intended for:

UPDATE table SET flag = 1
FROM table AS a1
LEFT OUTER JOIN table AS a2
ON (a1.other = a2.other AND a1.id < a2.id)
WHERE a2.ID IS NULL
and a1.name in ('x', 'y')

[reimer_pods]

I would think that syntax should be working in SQLA too (at least I noticed nothing incompatible), identical schma assumed. Did you give it a try?

[Former Member]

Yes, I gave it a try, but I think that there is no 'over (partion by xx)' or similar contruct in Adaptive Server 12. Ok, by now I realised that Adaptive Server and SQL Anywhere are different products. I am sorry I asked on the wrong site.

[Former Member]

Could someone delete the entire question. I thought this was a site for ASE and didnt know the difference to Sybase Anywhere. Sorry for that. I will repost on StackOverflow.

[Former Member]

I'm frankly a bit confused, because the StackExchange post doesn't address updates at all.

However - as far as I can tell, virtually any of the SQL queries given in the answers of that StackExchange post will work in SQL Anywhere. SQL Anywhere supports (of course) subqueries and joins to derived tables. SQL Anywhere 9.0.1 and up supports WINDOW functions, and you can use both MAX() and ROW_NUMBER() as window functions. Versions 10 and up supports the FIRST_VALUE() window function.

[Former Member]

I am sorry about the confusion. What I want to do is update a value, but the rows I want to update on are really the same as in the linked to question. I am using version 12.5.4 and will look further into your answer.

[Former Member]

Ah - you're using Sybase ASE 12.5.4. In that case, many of the solutions discussed in the StackExchange posting won't work, as ASE does not support WINDOW functions.

[Former Member]

Thanks Glenn. I wasnt aware of the different products. I will probably try it with an cursor next. The concern is, that the table must not be blocked for too long.