Jump to content

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


alexandre
 Share

Go to solution Solved by Barand,

Recommended Posts

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 by alexandre
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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      |
+------+---------+---------+----------+

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.