Pervasive SQL - Add a Rank Column to a Sorted Recordset

Question

In Pervasive 13, I'm looking for a way to add a rank column to a sorted record set.

For example, let's pretend that I have a customer table that has a field called profit (which is maintained to indicate the total profit from each customer). Instead of just sorting by profit, I also want to include each customer's rank based on profit, where the highest profit customer is ranked 1, the second highest is ranked 2, and so on.

select
     row_number() as "Rank"
    ,customer as "Customer"
from customers
order by profit desc

row_number(), above, is conceptual; it doesn't actually work in Pervasive 13, but I'm hoping there is something else that can achieve the same concept to produce a record set that looks like this:

Rank |Customer         |
-----+-----------------+
  1  |LUCRATIVE TECH   |
  2  |ROY INDUSTRIES   |
  3  |CRON INC.        |
  4  |FLEX PRODUCTS    |
  5  |CATCO CO.        |

Using only a SQL query, how can I produce a record set that contains a rank column like above in Pervasive 13?

I wonder how this can be done with just standard SQL (without engine-specific rank functions, which I'm so far unable to find in Pervasive 13). Current, I'm reading this [question](https://stackoverflow.com/questions/58517030/ranking-without-rank) for ideas. — Lonnie Best, Feb 21 '22 at 00:32

score 1 · Accepted Answer · answered Feb 21 '22 at 19:38

Something like this seems to work for me using a much smaller / manufactured dataset.

create table customers (id identity, customer char(20), profit integer);

insert into customers values (0,'cust1',5);
insert into customers values (0,'cust2',4);
insert into customers values (0,'cust3',1);
insert into customers values (0,'cust4',3);
insert into customers values (0,'cust5',2);
insert into customers values (0,'cust6',2);

select 
(select 1 + count(*)
        from customers c2
        where c2.profit < c.profit
       ) as "rank"
    , c.*
from customers c
order by "rank"

Results:

       rank            id   customer                    profit
===========   ===========   ====================   ===========
          1             3   cust3                            1
          2             5   cust5                            2
          3             4   cust4                            3
          4             2   cust2                            4
          5             1   cust1                            5

Close, but I'm wanting: "The higher the profit the lower the rank." Perhaps ordering by `c.profit desc` will achieve that. — Lonnie Best, Feb 22 '22 at 20:55
Now I understand why other SQL engines have created engine-specific functions for this task: this methodology becomes quite verbose on more complicated queries having multiple sub-queries and joins. Having a function like row_number(), reduces a lot of bloat (it can reduce the entire query's character count almost in half). — Lonnie Best, Feb 22 '22 at 21:04

Pervasive SQL - Add a Rank Column to a Sorted Recordset

1 Answers1