Sybase Alternative for DENSE_RANK() OVER (PARTITION BY

Question

I have a below SQL query, need to convert it into Sybase.

SELECT prd_name, DENSE_RANK() OVER (PARTITION BY prd_cat ORDER BY createddt) FROM product

Table Script:

CREATE table product(prd_name varchar(10),
                     prd_cat varchar(10), 
                     createddt datetime)
INSERT INTO product values('Product 1', 'Toy', CONVERT(DATE,'2017-05-30'))
INSERT INTO product values('Product 2', 'Toy', CONVERT(DATE,'2017-05-31'))
INSERT INTO product values('Product 3', 'Toy', CONVERT(DATE,'2017-05-31'))
INSERT INTO product values('Product 4', 'Toy1', CONVERT(DATE,'2017-05-29'))

Version: Adaptive Server Enterprise/15.7

Answer 1

Some versions of Sybase support window functions. I'm assuming that you don't have such a version.

You can replace it with a correlated subquery:

SELECT p.prd_name,
       (SELECT COUNT(DISTINCT createddt)
        FROM product p2
        WHERE p2.prd_cat = p.prd_cat AND p2.createddt < p.createddt
       )
FROM product p;