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

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?

##### Share on other sites

Is that a complicated way of asking about LIMIT?

##### Share on other sites

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

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

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

##### Share on other sites

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

##### Share on other sites

At present it's neither - just a typed example

##### Share on other sites

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 😄

##### 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)) {

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 by alexandre
##### Share on other sites

• Solution

Here's a quick and dirty solution

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

Still using mysqli !?

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

##### Share on other sites

44 minutes ago, alexandre said:

what is the rd1 and rd2

They are table aliases.

##### 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.

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
##### 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
, ( 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
##### 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_donated
, ( SELECT COUNT(*)+1
FROM donation rd2
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 |
+---------+----------------+---------------+------+```

##### 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_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.

##### 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
, @prevd := total_donated as donated
FROM (
SELECT name
, total_donated
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 |
+---------+------+------+----------+----------+```

##### 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
, @prevd := total_donated as donated
FROM (
SELECT name
, total_donated
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?

##### Share on other sites

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 ?

##### 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.

##### 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.

##### Share on other sites

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.

##### 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
, @prevd := total_donated as donated
FROM (
SELECT users_name
, total_donated
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>
</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 by alexandre
##### Share on other sites

Are you using MySql or MariaDB?

## Join the conversation

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

Reply to this topic...

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.