'Pervasive SQL - Add a Rank Column to a Sorted Recordset

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?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source