MySQL Partition By Rank

Here is an example in our system:

SELECT 
    customer_id,
    transaction_date,
    @curRank:=case
        when @ctid = customer_id then @curRank + 1
        else 1
    end AS rank,
    @ctid:=customer_id
FROM
    customer_transactions,
    (SELECT @curRank:=0, @ctid:=0) r
WHERE
    is_deleted = 'N'
        AND trans_type = 'Opening'
ORDER BY customer_id asc , transaction_date DESC

Reference:

1. http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/