SQL Number Rows without ROW_NUMBER() with Partition By ? MariaDB 10.1

Question

I would like to do this :

SELECT * , ROW_NUMBER() OVER (PARTITION BY Id ORDER BY score DESC, creationDate DESC) as num
FROM DB
ORDER BY score DESC, creationDate DESC

but in MariaDB 10.1, so row_number is not available. I managed to do this :

SELECT *, (@num:= @num + 1) AS num
from DB,
(SELECT @num:= 0 AS num1) AS c
order by score DESC, creationDate DESC

But I don't see how to put the partition by, so I could start a new count for any Id (the final aim is to order by the num created)

Thanks

Answer 1

You can express this as:

SELECT db.*,
       (@rn := IF(@id = id, @rn + 1,
                  IF(@id := id, 1, 1)
                 )
       ) as num
from (SELECT db.*
      FROM DB
      ORDER BY id, score DESC, creationDate DESC
     ) db CROSS JOIN
     (SELECT @rn := 0, @id := '') params;

Note: This uses a subquery to order the rows before doing the calculation. This was needed in the more recent pre-8.0 versions of MySQL. I don't know if it is really needed in MariaDB.