Count rows until value is reached

Question

I am trying to find a way to count the number of users until the number is reached. Here's somewhat of how my table is setup.

ID    Quantity
1           10
2           30
3           20
4           28

Basically, I want to organize the row quantity to be in order from greatest to least. Then I want it to count how many rows it takes from going from the highest quantity to whatever ID you supply it with. So for example, If I was looking for the ID #4, It would look through the quantity from from greatest to least, then tell me that it is row #2 because it took only 2 rows to reach it since it contains the 2nd highest quantity.

There is another way I can code this, but I feel it is too demanding of a resource and involves PHP. I can do a loop on my database based on the greatest to least, and every time it goes through another loop, I add +1. So, that way, I could do an IF statement to determine when it reaches my value. However, when I have thousands of values it would have to go through, I feel like that would be too resource demanding.

What about this: http://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table — rlanvin, Aug 21 '15 at 07:49
That looks like it does a lot more than what I want. What I want seems pretty feasible. I just need to count rows until I reach a certain value. — Josh Potter, Aug 21 '15 at 07:52
How it it more than what you want? All you want is to calculate the row number, right? Am I missing something? — rlanvin, Aug 21 '15 at 07:55
I tried using some of those, and I couldn't get it to work unfortunately. — Josh Potter, Aug 21 '15 at 08:06
Ok, what was the problem? Other solutions: http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select — rlanvin, Aug 21 '15 at 08:09
Here's the query: "SELECT count(*) +1 as total FROM mybb_users WHERE uid = '$memprofile[uid]' ORDER by points DESC" It says everyone is 2. — Josh Potter, Aug 21 '15 at 08:15
Please read the links I provided. This is not the solution. You need to use variables. — rlanvin, Aug 21 '15 at 08:42
You don't understand what I'm trying to do though. I'm not trying to count the total number of rows that exist where my conditions are at. I'm trying to count the number of rows until it reaches my conditions. — Josh Potter, Aug 21 '15 at 08:44
I posted a detailed answer. If indeed I don't understand and it's not what you are trying to do, feel free to comment (or even better, edit your original question to make it clearer). — rlanvin, Aug 21 '15 at 09:12

score 0 · Answer 1 · answered Aug 21 '15 at 07:57

Overall, this is a simple sort problem. Any data structure can give you the row of an item, with minor modifications in some cases.

If you are planning on using this operation multiple times, it is possible to beat the theoretical O(n log(n)) running time with an amortized O(log(n)) by maintaining a separate sorted copy of your table sorted by quantity. This reduces the problem to a binary search.

A third alternative is to maintain a virtual linked list of table entries in the new sort order. This would increase the insert times into the table to O(n), but would reduce this problem to O(1)

A fourth solution would be to maintain a virtual balanced tree, however, despite the good theoretical performance, this solution is likely to be extremely hard to implement.

score 0 · Answer 2 · edited May 23 '17 at 11:51

It might not be the answer you are expecting but: you can't "stop" the execution of a query after you reach a certain value. MySQL always generate the full result set before you can analyse it. This is because, it order to sort the results by Quantity, MySQL needs to have all the rows.

So if you want to do this is pure MySQL, you need to count the row numbers (as explained here MySQL - Get row number on select) in a temporary table and then select your ID from there.

Example:

SET @rank = 0;
SELECT *
FROM (
  SELECT Id, Quantity, @rank := @rank + 1 as rank
  FROM table
  ORDER BY Quantity
) as ordered_table
WHERE Id = 4;

If performance is an issue, you could probably speed this up a bit with an index on Quantity (to be tested). Otherwise the best way is to store the "rank" value in a separate table (containing only 2 columns: Id and Rank), possibly with a trigger to refresh the table on insert/update.

Count rows until value is reached

2 Answers2