CODEWITHSUNDEEP
mysqlsqlmariadb
Ne Mo
Ne Mo

Reputation: 15

Integer incremented by line displayed

This is team table :



+----+-------+--------+-------+
| id | alias | pwd    | score |
+----+-------+--------+-------+
|  1 | login | mdp    |     5 |
|  2 | azert | qsdfgh |    50 |
|  3 | test  | test   |   780 |
+----+-------+--------+-------+

This is activity table

+----+--------------+---------------------+-------+--------+
| id | localisation | name                | point | answer |
+----+--------------+---------------------+-------+--------+
|  1 | Madras       | Lancement du projet |     0 | NULL   |
|  2 | Valparaiso   | act1                |   450 | un     |
|  3 | Amphi        | act2                |    45 | deux   |
|  4 | Amphix       | act3                |   453 | trois  |
|  5 | Amphix       | act4                | 45553 | qautre |
|  6 | Madras       | Lancement du projet |     0 | NULL   |
|  7 | Valparaiso   | act1                |   450 | un     |
|  8 | Amphi        | act2                |    45 | deux   |
|  9 | Amphix       | act3                |   453 | trois  |
| 10 | Amphix       | act4                | 40053 | fin    |
+----+--------------+---------------------+-------+--------+

This is feed table :

+--------+---------------------+------------+--------+
| FeedId | ts                  | ActivityId | TeamId |
+--------+---------------------+------------+--------+
|      1 | 2023-01-10 00:02:06 |          1 |      3 |
|      2 | 2023-01-10 00:02:28 |          2 |      3 |
|      3 | 2023-01-10 00:21:13 |          3 |      3 |
|      4 | 2023-01-10 00:24:49 |          3 |      3 |
|      5 | 2023-01-10 00:30:58 |          1 |      1 |
+--------+---------------------+------------+--------+

I did this

MariaDB [sae]> SELECT @rownum:=@rownum+1 as 'Classement', t.alias, SUM(a.point) as total_points FROM activity a INNER JOIN feed f ON a.id = f.ActivityId INNER JOIN team t ON f.TeamId = t.id JOIN (SELECT @rownum:=0) r GROUP BY t.alias ORDER BY total_points DESC, Classement DESC;
+------------+-------+--------------+
| Classement | alias | total_points |
+------------+-------+--------------+
|          2 | test  |          540 |
|          1 | login |            0 |
+------------+-------+--------------+

Here the team with the highest number of points contains the ranking 2 instead of one and if I sort by ASC Ranking it does not change anything.

I wish to this :

+------------+-------+--------------+
| Classement | alias | total_points |
+------------+-------+--------------+
|          1 | test  |          540 |
|          2 | login |            0 |
+------------+-------+--------------+

Do you have any idea how to go about incrementing this "backwards" integer?

Upvotes: 0

Views: 61

Answers (3)

SelVazi
SelVazi

Reputation: 16043

This will handle the case if two or more teams have the same score. both of them will have the same ranking :

This is compatible with all versions of mysql and mariadb.

select @rank := CASE
    WHEN @totalval = total_points THEN @rank
    WHEN (@totalval := total_points) IS NOT NULL THEN @rank + 1
    WHEN (@totalval := total_points) IS NOT NULL THEN 1
END AS rank,
s.*
from (  
  SELECT t.alias, SUM(a.point) as total_points 
  FROM activity a 
  INNER JOIN feed f ON a.id = f.ActivityId 
  INNER JOIN team t ON f.TeamId = t.id 
  JOIN (SELECT @rank:=0, @totalval := 0) r 
  GROUP BY t.alias 
  ORDER BY total_points DESC
) as s;

Check it from here : https://dbfiddle.uk/7lKLu4Pw

Upvotes: 1

Georg Richter
Georg Richter

Reputation: 7476

Unless you are using an eoled version of MariaDB you should use WINDOW function RANK() instead of dealing with user variables.

Working with user variable increment returns the same value as ROW_NUMBER() but this is not correct, since teams with the same score should get the same ranking.

SELECT RANK() OVER (ORDER BY subq.total_points DESC) AS 'Classement', 
   subq.* FROM (
   SELECT team.alias, SUM(activity.point) AS total_points  FROM activity 
     JOIN feed ON activity.id = feed.ActivityId 
     JOIN team ON feed.TeamId = team.id  GROUP BY team.alias ) AS subq

Upvotes: 1

SelVazi
SelVazi

Reputation: 16043

Using the same logic as yours, You can do it as follows :

select @rownum:=@rownum+1 as 'Classement', s.*
from (
    SELECT t.alias, SUM(a.point) as total_points 
    FROM activity a 
    INNER JOIN feed f ON a.id = f.ActivityId 
    INNER JOIN team t ON f.TeamId = t.id 
    JOIN (SELECT @rownum:=0) r 
    GROUP BY t.alias 
    ORDER BY total_points DESC
) as s;

Check it here : https://dbfiddle.uk/TEz3UT97

Its working on mysql and mariadb

Upvotes: 1

Related Questions