how to count the number of rows preceding a given row?

[alexandre]

on phpmyadmin it is saying server type mariadb: Server version: 10.4.24-MariaDB - mariadb.org binary distribution

[alexandre]

but i think finally the order seem fine based on what they received and donated , except for the first place in the rank you can see they donated the same amount and the one who received the most is still in second place .. i think it is missing a tiny bit of accurracy still but is very close.

Edited November 11, 2022 by alexandre

[Barand]

In that case you need to uncomment the line in the query where it says "required by mariaDB users"

[alexandre]

4 minutes ago, Barand said:

In that case you need to uncomment the line in the query where it says "required by mariaDB users"

yes this worked for bringing back the order as it was but somehow the order is only based on the total received, with the mariadb line commented i think it was closer to take in count the total_donated.

[alexandre]

all in all , now this is working fine, i thank you a lot for your help. 

[Barand]

The purpose of the subquery is to sort the data into the required order (ORDER BY total_received DESC, total_donated ASC) before applying the rankings.

MariaDB ignores ORDER BY in subqueries. The way round it is the large LIMIT value to force it to write to a temporary table on disc.

[alexandre]

that is good to know thank you, those are things that normal people would break their head to normally try to understand what create a certain behavior, after that people still wonder why i prefer asking questions on forums than searching on google, this is a pretty good example why. lets say i would never have guessed this by myself.

[alexandre]

35 minutes ago, Barand said:

The purpose of the subquery is to sort the data into the required order (ORDER BY total_received DESC, total_donated ASC) before applying the rankings.

MariaDB ignores ORDER BY in subqueries. The way round it is the large LIMIT value to force it to write to a temporary table on disc.

does this means that for example , i want to make a load more button using thise query if i have to set a limit to a super high number, will this make me unable to make this load more button for which i guess i have to separate the number of rows pulled so with a high limit , will be impossible no ?

[Barand]

No problem. Make that query a subquery of your query doing the pagination so that you paginate the results of the query. For example

 
            SELECT rank
                 , name
                 , donated
                 , received
            FROM (
            
                -- THIS IS THE QUERY TO DO THE RANKING - NOW A SUBQUERY
                 
                    SELECT id
                         , name
                         , @seq := @seq + 1 as seq
                         , @rank := CASE WHEN total_received = @prevr
                                         THEN CASE WHEN total_donated = @prevd
                                                   THEN @rank
                                                   ELSE @seq
                                               END
                                          ELSE @seq
                                          END as rank
                         , @prevr := total_received as received
                         , @prevd := total_donated as donated
                    FROM (
                            SELECT id 
                                 , name
                                 , total_donated
                                 , total_received
                            FROM donation
                            WHERE total_participant = 1
                            ORDER BY total_received DESC, total_donated ASC
                            LIMIT 18446744073709551615
                         ) ordered 
                         JOIN (
                                SELECT @rank := 0, @seq := 0, @prevd := 0, @prevr := 0
                              ) init
                    ) ranked
                    
                -- END OF RANKING SUBQUERY
                
            ORDER BY rank LIMIT ?, ?          
            ";

Running that query with

• LIMIT 0, 4, then
• LIMIT 4,4

gave 2 pages...

Page 1
+------+---------+---------+----------+
| rank | name    | donated | received |
+------+---------+---------+----------+
| 1    | Dasher  | 250     | 500      |
| 2    | Cupid   | 370     | 400      |
| 2    | Prancer | 370     | 400      |
| 4    | Comet   | 370     | 380      |
+------+---------+---------+----------+
Page 2
+------+---------+---------+----------+
| rank | name    | donated | received |
+------+---------+---------+----------+
| 5    | Dancer  | 510     | 200      |
| 6    | Vixen   | 200     | 100      |
| 7    | Donner  | 510     | 100      |
+------+---------+---------+----------+

 

[alexandre]

thats awesome , so just to be sure , this query doesnt create the pagination links right ? because i read about an automatic pagination but i cant remember where .. this is kinda frustrating 😅

[Barand]

8 hours ago, alexandre said:

so just to be sure , this query doesnt create the pagination links right ?

Right. The links need to be created on your web page, SQL is confined to the DB server. This query fetches the chosen page of results after a link is clicked..

[alexandre]

alright, thank you very much.