Oracle RANK() function to Sybase ASE

Hi experts,

Is there a way of converting (or re-writing) an ORACLE RANK() function into Sybase ASE T-SQL?

I know this is possible in Sybase IQ but I have not seen this function in ASE and was wondering how I could get a query containing RANK to work in Sybase ASE.

Thanks,
SybaseUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shru_0409Commented:
SELECT TOP 10 * FROM T

try this


0
IncisiveOneCommented:
Nope.

TOP x will return the first x rows based on your ORDER BY (or the implicit ordering resulting from the index scan).

RANK() returns the position of the row in the result set, OVER (based on) some other identified criteria, such as another column value.

RANK() is easy to implement in Orable because it is ROW_ID based.  Difficult in Sybase because it is purely set oriented.  You will have to use a cursor, ugly as that is.  

I can give you a code struct that does exactly what a cursor does, minus the cursor class of locks and minus the internal overhead; ie. it performs better and allows more concurrency.   An enhanced version is great for pagination as well (return 'pages' of 50 rows at a time from a 7,000 row result set), but it requires a handshake from the front end/web server.

Cheers

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SybaseUkAuthor Commented:
Does anyone know how to configure ASE to support CIS rpc procedures?
0
IncisiveOneCommented:
SybaseUk

The way it works is, you need to close the question, award points, and ask a new question.  You will get a much better response, the only people watching this thread are those who have chosen to watch an "Oracle Rank()" question.

Cheers

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.