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

[alexandre]

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?

[requinix]

Is that a complicated way of asking about LIMIT?

[alexandre]

kind of i didnt think about searching if i could use a variable with the limit clause. 

[alexandre]

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 November 10, 2022 by alexandre

[Barand]

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

 

[alexandre]

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 

[alexandre]

is this the output of a query you did or you managed arrays ?

[Barand]

At present it's neither - just a typed example

[alexandre]

oh ok , i was just wondering . you put so much effort into this , i hope i will succeed so i can pay it forward to you 😄

[alexandre]

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.

Edited November 10, 2022 by alexandre

[Barand]

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 !?

[alexandre]

 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?

[Barand]

44 minutes ago, alexandre said:

what is the rd1 and rd2

They are table aliases.

Easier to write rd2.total_received than random_donation_clash2.total_received

[alexandre]

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.

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 November 11, 2022 by alexandre

[alexandre]

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 November 11, 2022 by alexandre

[Barand]

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

 

[kicken]

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.

 

[Barand]

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

 

[alexandre]

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?

[alexandre]

i would just need to know is that a pdo query? because the "@" like @seq ,@rank, @prevr and @prevd i dont really know what they represent or this ":=" i would need some explanations if you can ?

[Barand]

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.

[alexandre]

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.

[Barand]

23 minutes ago, Barand said:

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

 

[alexandre]

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:

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 November 11, 2022 by alexandre

[Barand]

Are you using MySql or MariaDB?