Sybase ASE: Compute ranks per grouped category

Question

I'm using Sybase ASE 15.7 which does not support RANK() or any other windowing functions.

I'm trying to compute ranks grouped per product category.

Consider the following PRODUCTS table with approximately 2 million records.

PROD_CATEGORY | PROD_NAME          | PROD_SALES  
---------------------------------------------  
Laptops       | Dell Inspiron      | 1000
Laptops       | Lenovo Thinkpad    | 800
Laptops       | Dell Latitude      | 500
Laptops       | Acer Aspire        | 1000
Printers      | Canon Pixma        | 2500
Printers      | HP OfficeJet       | 800
Printers      | HP DeskJet         | 1200
Desktops      | Lenovo ThinkCentre | 900
Desktops      | Asus Chromebox     | 300
Desktops      | HP Pavilion        | 1500

I'm trying to write a SQL query that'll compute ranks grouped per product category like below:

PROD_CATEGORY | PROD_NAME          | PROD_SALES | SALES_RANK  
------------------------------------------------------------  
Laptops       | Dell Inspiron      | 1000       | 1 
Laptops       | Lenovo Thinkpad    | 800        | 2
Laptops       | Acer Aspire        | 800        | 2
Laptops       | Dell Latitude      | 500        | 4
Printers      | Canon Pixma        | 2500       | 1
Printers      | HP DeskJet         | 1200       | 2
Printers      | HP OfficeJet       | 800        | 3
Desktops      | HP Pavilion        | 1500       | 1
Desktops      | Lenovo ThinkCentre | 900        | 2
Desktops      | Asus Chromebox     | 300        | 3

Note that products with the same PROD_SALES value generate a tied rank and the subsequent ranks have a gap in them.

To generate a rank without the RANK() function for all rows in the table, the standard SQL query given here works well for the table with 2 million rows.

To generate the rank grouped per category, I tried the SQL query given here without success as the query takes a long time and did not complete execution.

Is there a way to generate this rank per grouped category that will work with Sybase ASE?

This question is similar to this SO question that was asked for MySQL.

Answer 1

You can compute the rank with a correlated subquery:

select p.*,
       (select 1 + count(*)
        from products p2
        where p2.prod_category = p.prod_category and p2.prod_sales > p.prod_sales
       ) as rnk
from products p;