Creating a rank from the smallest amount to the largest amount group by multiple columns

Question

Currently, I am using MySQL 5.6.30 and I need a your help.

Here is data in the table which name is temp_work

+----+-----------+----------+----------+--------+---------+-------+---------+
| id | client_id | account  | category | t_year | t_month | t_day | amount  |
+----+-----------+----------+----------+--------+---------+-------+---------+
| 1  | 100       | Saving   | deposit  | 2016   | 12      | 14    | 100.84  |
| 2  | 100       | Checking | withdraw | 2016   | 12      | 15    | 300.24  |
| 3  | 100       | Checking | deposit  | 2016   | 12      | 29    | 60.00   |
| 4  | 101       | Saving   | Withdraw | 2016   | 12      | 29    | 245.16  |
| 5  | 100       | Saving   | Withdraw | 2016   | 12      | 30    | 2200.00 |
| 6  | 100       | Checking | Withdraw | 2016   | 12      | 30    | 2372.16 |
| 7  | 100       | Saving   | deposit  | 2016   | 12      | 30    | 4327.00 |
| 8  | 101       | Checking | Withdraw | 2017   | 1       | 3     | 80.00   |
| 9  | 101       | Checking | Withdraw | 2017   | 1       | 3     | 1033.45 |
| 10 | 100       | Saving   | Withdraw | 2017   | 1       | 3     | 1339.16 |
| 11 | 100       | Checking | deposit  | 2017   | 1       | 4     | 140.00  |
| 12 | 100       | Checking | Withdraw | 2017   | 1       | 4     | 216.73  |
| 13 | 101       | Checking | Withdraw | 2017   | 1       | 4     | 1261.72 |
+----+-----------+----------+----------+--------+---------+-------+---------+

and I need a rank from lowest amount to largest amount group by client_id, account, category, t_year, t_month.

So, This is what I expected or similar

+-----------+----------+----------+--------+---------+---------+------+
| client_id | account  | category | t_year | t_month | amount  | rank |
+-----------+----------+----------+--------+---------+---------+------+
| 100       | Checking | deposit  | 2016   | 12      | 60.00   | 1    |
| 100       | Checking | deposit  | 2017   | 1       | 140.00  | 1    |
| 100       | Checking | withdraw | 2016   | 12      | 300.24  | 1    |
| 100       | Checking | withdraw | 2016   | 12      | 2327.16 | 2    |
| 100       | Checking | Withdraw | 2017   | 1       | 216.73  | 1    |
| 100       | Saving   | deposit  | 2016   | 12      | 100.84  | 1    |
| 100       | Saving   | deposit  | 2016   | 12      | 4327.00 | 2    |
| 100       | Saving   | Withdraw | 2016   | 12      | 2200.00 | 1    |
| 100       | Saving   | Withdraw | 2017   | 1       | 1339.16 | 1    |
| 101       | Checking | Withdraw | 2017   | 1       | 80.00   | 1    |
| 101       | Checking | Withdraw | 2017   | 1       | 1033.45 | 2    |
| 101       | Checking | Withdraw | 2017   | 1       | 1261.72 | 3    |
| 101       | Saving   | Withdraw | 2016   | 12      | 245.16  | 1    |
+-----------+----------+----------+--------+---------+---------+------+

Here is my first try:

Select  
tw1.client_id, tw1.account, tw1.category, tw1.t_year, tw1.t_month, tw1.amount
,@rownum = case when @tmonth <> tw1.t_month then 0 else @rownum + 1 end as ranking
,@tmonth := t_month as cmonth
From
( Select    @rownum := 0 from dual ) as r,
( Select    @tmonth := 0 from dual ) as m,
( Select    client_id, account, category, t_year, t_month, amount
  From      temp_work   as  tw
  Order by  client_id, account, category, t_year, t_month, amount) as tw1

Second try:

Select  tw1.client_id, tw1.account, tw1.category, tw1.t_year, tw1.t_month, tw1.amount
    ,@rownum = case when concat(@tyear, '-', @tmonth) <> concat(t_year, '-', t_month) then 0 else @rownum + 1 end as rank
    ,@tyear := t_year as cyear
    ,@tmonth := t_month as cmonth        
From
( Select    @rownum := 0 ) as r,
( Select    @tyear := 0 ) as y,
( Select    @tmonth := 0 ) as m,
(
Select  client_id, account, category, t_year, t_month, amount
From    temp_work   
Order by    tw.client_id, tw.account, tw.category, t_year, t_month, tw.amount
) as tw1

However, above queries gives me the same result like below.

+-----------+----------+----------+--------+---------+---------+------+
| client_id | account  | category | t_year | t_month | amount  | rank |
+-----------+----------+----------+--------+---------+---------+------+
| 100       | Checking | deposit  | 2016   | 12      | 60.00   | 1    |
| 100       | Checking | deposit  | 2017   | 1       | 140.00  | 1    |
| 100       | Checking | withdraw | 2016   | 12      | 300.24  | 1    |
| 100       | Checking | withdraw | 2016   | 12      | 2327.16 | 0    |
| 100       | Checking | Withdraw | 2017   | 1       | 216.73  | 1    |
| 100       | Saving   | deposit  | 2016   | 12      | 100.84  | 1    |
| 100       | Saving   | deposit  | 2016   | 12      | 4327.00 | 0    |
| 100       | Saving   | Withdraw | 2016   | 12      | 2200.00 | 1    |
| 100       | Saving   | Withdraw | 2017   | 1       | 1339.16 | 1    |
| 101       | Checking | Withdraw | 2017   | 1       | 80.00   | 1    |
| 101       | Checking | Withdraw | 2017   | 1       | 1033.45 | 0    |
| 101       | Checking | Withdraw | 2017   | 1       | 1261.72 | 0    |
| 101       | Saving   | Withdraw | 2016   | 12      | 245.16  | 1    |
+-----------+----------+----------+--------+---------+---------+------+

Please give me the hint to solve this problem.

Thank you very much.

That's a really difficult schema to work with. It's often better if your amounts are all +/- depending on their action, where a deposit is + and a withdrawl is -. There are many approaches to organize this, but the [general ledger](https://en.wikipedia.org/wiki/General_ledger) is a good place to start. It'd be a good idea to use the native `DATE` type for transactions as well. Independent month/year columns are super obnoxious to work with. — tadman, Apr 04 '17 at 20:52
Possible duplicate of [Rank function in MySQL](http://stackoverflow.com/questions/3333665/rank-function-in-mysql) — jordiburgos, Apr 04 '17 at 21:24

score 1 · Answer 1 · answered Apr 04 '17 at 22:48

1

Try rearranging your Order by statement to have your "amount" the second value in the order by. This will get you closer to what you want.

Order by client_id, amount, account, category, t_year, t_month

The order in which you list your columns will decide the order they are sorted.

For more info: https://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html

answered Apr 04 '17 at 22:48

JT H

19
3

Thank you for your help. I tried but it does not work. The result is same – Younghyuk Kim Apr 05 '17 at 13:02

score 0 · Accepted Answer · answered Apr 05 '17 at 01:47

Consider a correlated count aggregate subquery for ranking:

SELECT t.client_id, t.account, t.category, t.t_year, t.t_month, t.amount,
       (SELECT COUNT(*) FROM temp_work sub
        WHERE sub.amount <= t.amount 
        AND sub.client_id = t.client_id
        AND sub.account = t.account
        AND sub.t_year = t.t_year
        AND sub.t_month = t.t_Month) AS rank
FROM temp_work t
ORDER BY t.client_id, t.account, t.category, t.t_year, t.t_month, t.amount

Creating a rank from the smallest amount to the largest amount group by multiple columns

2 Answers2