CODEWITHSUNDEEP
mysqlsqlmariadb
Elcalli
Elcalli

Reputation: 13

MariaDB 10.1.44 number rows without row_number()

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 db2.*,
               (@rn := IF(@Id = Id, @rn + 1,
                          IF(@Id := Id, 1, 1)
                         )
               ) as num
            from (SELECT *
                  FROM DB 
                  ORDER BY Id DESC , score DESC, creationDate DESC
             ) db2 CROSS JOIN
             (SELECT @rn := 0, @Id := '') params

But the results are not the same. I Would like to have this :

1 1.0
1 2.0
1 3.0
1 4.0
1 5.0
1 6.0
1 7.0
1 8.0
2 1.0
2 2.0
6 1.0
6 2.0
6 3.0
6 4.0
8 1.0
10 1.0
10 2.0
10 3.0
12 1.0
14 1.0 
15 1.0
16 1.0
16 2.0 
16 3.0
16 4.0

But I got :

6   1.0
1   1.0
1   2.0
1   4.0
1   3.0
1   5.0
1   6.0
1   1.0
2   2.0
2   1.0
1   7.0
8   1.0
6   2.0
10  1.0
10  1.0
10  1.0

So the problem is : for Id = 1, it seems to work but I got 2 times a 1 as num. And for other Ids, like for 10, it does not work at all... I don't see why

Thanks

Upvotes: 1

Views: 1030

Answers (2)

Akina
Akina

Reputation: 42728

Why not simply

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

Upvotes: 0

nbk
nbk

Reputation: 49385

The cause is that mariadb follows the standards, which say that a result set is unorderd, as long there is no Limit.

I don't know, why you don't use row_number. but use the following query

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

Upvotes: 1

Related Questions