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?
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