CODEWITHSUNDEEP
sqliteranking
Deepak
Deepak

Reputation: 1066

Update the rank column based on the total of a group

I wrote the following code in SQLite:

CREATE TABLE payments (
  customerNumber REAL NOT NULL,
  checkNumber TEXT NOT NULL,
  paymentDate TEXT NOT NULL,
  amount REAL NOT NULL,
  rank REAL default 0,
  PRIMARY KEY  (customerNumber,checkNumber)
);

insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(103,'HQ336336','2004-10-19 00:00:00',6066.78);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(103,'JM555205','2003-06-05 00:00:00',14571.44);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(112,'BO864823','2004-12-17 00:00:00',14191.12);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(112,'HQ55022','2003-06-06 00:00:00',32641.98);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(121,'MA302151','2004-11-28 00:00:00',34638.14);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(121,'KI831359','2004-11-04 00:00:00',17876.32);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(161,'KG644125','2005-02-02 00:00:00',12692.19);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(161,'NI908214','2003-08-05 00:00:00',38675.13);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(181,'CM564612','2004-04-25 00:00:00',22602.36);
insert  into payments(customerNumber,checkNumber,paymentDate,amount) values(181,'GQ132144','2003-01-30 00:00:00',5494.78);

Now my question is, How can I update the rank column in the table based on the ranking of "total(amount) from payments group by customerNumber"?

Upvotes: 3

Views: 1340

Answers (1)

Michał Powaga
Michał Powaga

Reputation: 23183

Edit (removed SQL Server code, SQLite doesn't support joins in update statements):

I think this should be ok:

drop table if exists ranks;

create temp table ranks (
    rank integer primary key,
    customerNumber real,
    total real
);

insert into ranks
select null, customerNumber, total(amount) as total
from payments
group by customerNumber
order by total desc;


update payments set
    rank = (
        select rank from ranks 
        where customerNumber = payments.customerNumber
    );

select * from payments;

Added:

Here is very very dirty way to do it in one statement, it uses very specific subqueries to rank (it's some kind of SQLite row number) each customer. As I've said, many many sybqueries but that's the price to get it work in one query without using join in update.

update payments set
    rank = (
        select
            (select count(0) from (select total(amount) as t, customerNumber as c
                    from payments group by customerNumber order by t desc) t1
            where t1.t >= t2.t) as rank
        from (select total(amount) as t, customerNumber as c from payments group by customerNumber order by t desc) 
        t2 where t2.c = payments.customerNumber order by t2.t desc
    );

Upvotes: 3

Related Questions