convert rank() over (partition .... in mysql 5.7

Question

i am trying to convert the following sql in mysql 5.7

 SELECT T.STOREID AS STOREID,
           T.ARTID AS ARTID,
           T.TOTAMOUNT,
           MAX(T.HOUR) AS HOUR,
           RANK() OVER (PARTITION BY T.STOREID, T.ARTID
                        ORDER BY T.STOREID, T.ARTID, T.HOUR DESC) AS RN
    FROM T
ORDER BY T.STOREID,
         T.ARTID

this is working perfect in oracle but does work in mysql because only mysql 8 and up is supporting this. Can anyone help me in order to have the same results in mysql 5.7

I don't think there's a SQL solution to it in MySQL 5.7. You'll need to retrieve the rows in an orderly fashion and post-process the data in your app to compute a rank. It's more than a `ROW_NUMBER()`, and I think the related answer does not address your case. — The Impaler, Apr 18 '20 at 21:04

score 6 · Answer 1 · answered Apr 18 '20 at 21:27

As a starter, a few notes about your original query:

it is not valid SQL; you have an aggregate function in the select clause (max(t.hour)), but no group by clause
the order by clause of rank() is not optimized: it starts with columns that are part of the partition by clause, which is unecessary (we already know that all rows in the partition have the same values for these columns)

So I'll assume that the query you want to rewrite is:

select 
    t.storeid as storeid,
    t.artid as artid,
    t.totamount,
    t.hour,
    rank() over(partition by t.storeid, t.artid order by t.hour desc) rn
from t
order by t.storeid, t.artid

Now, in MySQL 5.7, one method to emulate rank() uses a correlated subquery:

select 
    t.storeid as storeid,
    t.artid as artid,
    t.totamount,
    t.hour,
    (
        select 1 + count(*)
        from t t1
        where 
            t1.storeid = t.storeid 
            and t1.artid = t.artid
            and t1.hour > t.hour

    ) as rn
from t
order by t.storeid, t.artid

Note that this is really less efficient that window functions, since the subquery must be executed for each and every row in the original table. Other solutions typically involve user variables.

convert rank() over (partition .... in mysql 5.7

1 Answers1

Linked