Jump to content

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


alexandre
 Share

Go to solution Solved by Barand,

Recommended Posts

i have been looking around and could not find a solution to count the number of rows present between 0 and a given row.

my given row needs to represent the offset of the total number of pulled rows from the query. can it be done by a simple query? or i would need to pull the whole table and then manipulate the data to define that offset myself by counting the arrays in the array until it meets a condition or so?

Link to comment
Share on other sites

my problem is that i need to define that offset row by the name and total_received , since they are pulled in desc order to create a ranking of the donations it wont show in the order they have submitted to the table. by that i mean that i would not be able to use a auto-incrementing collumn to aim at the row i need ..

 

Edited by alexandre
Link to comment
Share on other sites

Are you saying you want to know the ranking of each amount? EG

+----------+-----------+------+
| Name     | Donation  | Rank |
+----------+-----------+------+
| Comet    |    650    |   1  |
| Cupid    |    510    |   2  |
| Donner   |    510    |   2  |
| Blitzen  |    480    |   4  |
| Dancer   |    410    |   5  |
| Prancer  |    370    |   6  |
| Dasher   |    250    |   7  |
| Vixen    |    200    |   8  |
+----------+-----------+------+

 

Link to comment
Share on other sites

4 minutes ago, Barand said:

Are you saying you want to know the ranking of each amount? EG

+----------+-----------+------+
| Name     | Donation  | Rank |
+----------+-----------+------+
| Comet    |    650    |   1  |
| Cupid    |    510    |   2  |
| Donner   |    510    |   2  |
| Blitzen  |    480    |   4  |
| Dancer   |    410    |   5  |
| Prancer  |    370    |   6  |
| Dasher   |    250    |   7  |
| Vixen    |    200    |   8  |
+----------+-----------+------+

 

yes this looks like it,  only mine is ordered by the total received by participant but thats basicly the same. i was trying to display the rank of the participants in the ranking panel 

Link to comment
Share on other sites

my query right now looks like this :

	$stmt = 'SELECT users_name, total_donated, total_received FROM random_donation_clash WHERE total_participant = 1 ORDER BY total_received DESC';
	$result = mysqli_query($con, $stmt);
	if (mysqli_num_rows($result) > 0) {
	    while ($result_row = mysqli_fetch_assoc($result)) {
$received[] = $result_row['total_received'];

it makes me a ranking well ordered but still quite simple , if i wanted to push it a bit farther i would calculate the rate between the loss and gains and order the ranking differently. but as you can see i am a bit struggling with basic stuff so i will keep it simple and just look to add what you just shown above. here is my output.image.thumb.png.de7e4aa45347fabbd82b47f1a16a21e1.png

Edited by alexandre
Link to comment
Share on other sites

  • Solution

Here's a quick and dirty solution

SELECT users_name
      , total_donated
      , total_received 
      , ( SELECT COUNT(*)+1 
          FROM random_donation_clash rd2 
          WHERE rd2.total_received > rd1.total_received
          ) AS rank
FROM random_donation_clash rd1
WHERE total_participant = 1 
ORDER BY total_received DESC

Still using mysqli !?

Link to comment
Share on other sites

 i really tried to like pdo but the synthax of the parameter felt repelling and i couldnt keep up with developping my things. on the other hand i have a pdo connection file at hand. only if i can find a solution with mysqli first i will always look for it. i really like mysqli extension and the synthax as well thats mostly why.

also for the query , can i ask what is the rd1 and rd2, are they collumns that you added?

Link to comment
Share on other sites

this almost does it just that the rank end up not a continuous rank. it jumps to another number. and keep going up, i think it is counting the duplicates that are not displayed. thats why i am using the total_participant = 1 as WHERE clause, every participants have one record with total_participant= 1 all their other records this collumn equal zero.image.thumb.png.5363929050bf25f4a9dcc7422f9e1fe2.png

if i add the total_participant WHERE clause to the first part of alias it increases the accuracy of the rank , now the only problem is that this is jumping the position in the rank after two or more have an equal total_received, probably there is a way that i could compare the total_donated and if it is lower then the other participant it go higher in rank than the one having higher difference between received and donated. 

Edited by alexandre
Link to comment
Share on other sites

something i am wondering is if its possible to use conditional in a query like if total received is equal and totldonated < then go up in rank , something like that because the only problem is caused by the equal records who are being counted.

	$stmt = 'SELECT users_name
      , total_donated
      , total_received
      , ( SELECT COUNT(*)+1
          FROM random_donation_clash rd2
          WHERE rd2.total_received > rd1.total_received AND total_participant = 1
          ) AS rank
FROM random_donation_clash rd1
WHERE total_participant = 1
ORDER BY total_received DESC';

 

Edited by alexandre
Link to comment
Share on other sites

Perhaps

SELECT * FROM donation;
+----+---------+---------------+----------------+-------------------+
| id | name    | total_donated | total_received | total_participant |
+----+---------+---------------+----------------+-------------------+
|  1 | Comet   |           370 |            380 |                 1 |
|  2 | Cupid   |           370 |            400 |                 1 |
|  3 | Donner  |           510 |            100 |                 1 |
|  4 | Blitzen |           480 |            350 |                 0 |
|  5 | Dancer  |           510 |            200 |                 1 |
|  6 | Prancer |           370 |            400 |                 1 |
|  7 | Dasher  |           250 |            500 |                 1 |
|  8 | Vixen   |           200 |            100 |                 1 |
+----+---------+---------------+----------------+-------------------+

SELECT name
      , total_received
      , total_donated
      , ( SELECT COUNT(*)+1
          FROM donation rd2
          WHERE ( rd2.total_received > rd1.total_received
                    OR  ( rd2.total_received = rd1.total_received
                          AND rd2.total_donated < rd1.total_donated
                          )
                  )
                  AND total_participant = 1
          ) AS rank
FROM donation rd1
WHERE total_participant = 1
ORDER BY total_received DESC, total_donated ASC

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

 

Link to comment
Share on other sites

FYI, if you're using Mysql 8.0 series you can simplify the query by using the window function.  One of rank, dense_rank, or row_number would probably give you what you want.

For example:

SELECT name
      , total_received
      , total_donated
      , rank() over (ORDER BY total_received DESC, total_donated ASC) as the_rank
FROM donation rd1
WHERE total_participant = 1
ORDER BY the_rank;
| name    | total_received | total_donated | the_rank |
| ------- | -------------- | ------------- | -------- |
| Dasher  | 500            | 250           | 1        |
| Cupid   | 400            | 370           | 2        |
| Prancer | 400            | 370           | 2        |
| Comet   | 380            | 370           | 4        |
| Dancer  | 200            | 510           | 5        |
| Vixen   | 100            | 200           | 6        |
| Donner  | 100            | 510           | 7        |

If you're not on Mysql 8.0, you'll have to stick with Barands way.

 

  • Like 1
Link to comment
Share on other sites

@kicken Nice. I'll have to have another go at installing v8.0

 

as I mentioned, that was the quick and dirty way. IMO a better way (pre v8.0) is as below. In my tests, this method was typically around 6x faster on that small dataset. Savings would be greater with larger sets.

Quick n dirty        : 0.0034 seconds
Long n clean         : 0.0005 seconds

SQL

SELECT 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 name 
             , total_donated
             , total_received
        FROM donation
        WHERE total_participant = 1
        ORDER BY total_received DESC, total_donated ASC
        -- LIMIT 18446744073709551615         --(required by mariaDB users only - bug workaraound)
     ) ordered 
     JOIN (
            SELECT @rank := 0, @seq := 0, @prevd := 0, @prevr := 0
          ) init  ;

Sample results

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

 

Link to comment
Share on other sites

3 hours ago, Barand said:

@kicken Nice. I'll have to have another go at installing v8.0

 

as I mentioned, that was the quick and dirty way. IMO a better way (pre v8.0) is as below. In my tests, this method was typically around 6x faster on that small dataset. Savings would be greater with larger sets.

Quick n dirty        : 0.0034 seconds
Long n clean         : 0.0005 seconds

SQL

SELECT 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 name 
             , total_donated
             , total_received
        FROM donation
        WHERE total_participant = 1
        ORDER BY total_received DESC, total_donated ASC
        -- LIMIT 18446744073709551615         --(required by mariaDB users only - bug workaraound)
     ) ordered 
     JOIN (
            SELECT @rank := 0, @seq := 0, @prevd := 0, @prevr := 0
          ) init  ;

Sample results

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

 

im sorry i already marked your other example as the solution but this one would deserve a markup on it. this is exactly the output that i was thinking about. would you mind if i use this code?

Link to comment
Share on other sites

It's SQL (Structured Query Language - the language that all database queries use) Neither PDO nor mysqli - it will work with either.

The '@' prefix is used for user variables. In this case I use those @seq, @rank, @prevr and @prevd for the

  • sequence count,
  • rank,
  • previous record's received value and
  • previous record's donation value

so that I can store the values in one record and refer to them in the next one. The := is the assignent operator for user variables in SQL.

Link to comment
Share on other sites

i see thank you for the explanations. if iwanted to use this query with mysqli, would i have to replace the prefixes and sql assignement operator by something else, or this work as it is in a normal query?  as i am asking i didnt tried it yet , i am more trying to understand it first.

Link to comment
Share on other sites

so as i am trying it , it is telling me that the array key are not defined for total_donated and total_received. i past it this way:

$stmt = 'SELECT users_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 users_name
					             , total_donated
					             , total_received
					        FROM random_donation_clash
					        WHERE total_participant = 1
					        ORDER BY total_received DESC, total_donated ASC
					        -- LIMIT 18446744073709551615         --(required by mariaDB users only - bug workaraound)
					     ) ordered
					     JOIN (
					            SELECT @rank := 0, @seq := 0, @prevd := 0, @prevr := 0
					          ) init';
					$result = mysqli_query($con, $stmt);
					if (mysqli_num_rows($result) > 0) {
					    while ($result_row = mysqli_fetch_assoc($result)) {
				$seq[] = $result_row['seq'];
								echo"<div class='rankingtrue2'>
								<div class='ranking_wrapper'>
									 <table class='rankingtable3'>

					            <tr>
					               <td>rank</td><td>username</td><td>total donated</td><td>total_received</td>
					            </tr>
					            <tr>
											<td class='remain1'>" . $result_row['seq']. "</td>
					                <td class='remain1'>" . $result_row['users_name']. "</td>

					                <td class='remain3'>" . $result_row['total_donated']. "</td>
				                  <td class='remain4'>" . $result_row['total_received']. "</td>
					            </tr></table></div></div><br><br>";

				}
				}

i changed the array key to donated and received and here is the output:

image.thumb.png.1500aff3fe03e11ba46d9baeddf25daf.png

you can see the seq row is fine but the order is all messed up, i feel like i am doing something wrong somewhere.

Edited by alexandre
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.