Hi,
Say we have a list generated like:
SELECT * FROM users WHERE account_balance > 0
How would you then get the rank of the user = john_doe from this generated list? Without looping through this list to find the rank but have MySQL deliver it which would be more efficient than a For Loop to find the rank of a given user from this list.
Regards,
SELECT ( SELECT COUNT(*) + 1
FROM users
WHERE account_balance
> t.account_balance ) AS rank
FROM users AS t
WHERE user = 'John Doe'
Man, your SQL commands are always so complex I cannot quite decipher them 
So let me instead give you the actual MySQL command and ask you to apply your suggestion to it. Here is the actual MySQL command from which I need to find the rank of a given User_id:
SELECT keyword, user_id, cost FROM keywords WHERE key_word LIKE’%web%’ order by cost desc;
So once the above list is generated then for example I need to know the rank, aka the row number, of user_id = xyz
ThanX,
why do you want to generate a list?
i thought you wanted john doe’s rank
what is the list for?
Hi,
Well we need to generate this list for various reasons, for all the reasons that one generates a list from a MySQL DB. From which list then we need to know what is the rank of X entry for various reasons.
John Doe thing was just a generic problem description, but since your code was too complex for me to make sense out of it, I decided best was to give you the actual select which generates the list.
ThanX.
okay, if you are creating a list, i don’t understand why you can’t compute the rank
just loop over the list with your application language and count the rows, or do an ArrayFind function or something
the information you want is in the list, there is no need to go back to mysql
Well of course I can write the code in Php that would find the rank of a given row within the list, but I was wondering if there is a command in MySQL that would do that for us? I guess not. So off I go writing it in Php.
actually there is
except it isn’t a command, it’s a query (which i gave you earlier)
the reason to do it in php is for efficiency – far quicker to extract something from data already in memory than to issue another call to mysql
Just a suggestion, why not calculate the rank or whatever you need when you insert data in the account_balance table? Update users table afterwards and you don’t have to calculate anything on the fly as the data is pre-calculated so you just need to pull it out.
That way, you calculate once - it’s more efficient, and you can work with the dataset easily, query it, order it and so on.
furicane, your suggestion works if the data in the table doesn’t change. If it does, and that is the most likely scenario, you’d have to recalculate the rank so no time is saved. We’re talking fractions of a second here. Also the data is already retrieved with the call to the database in php and the OP would be running a FOR loop or a WHILE so why not calculate it at that time with a variable in php?
Yes, when the data is changed you recalculate the value.
Then you ask yourself - do you have more requests to the database when running a report or item listing or do you have more CRUD operations.
Why would you calculate something on the fly when you can calculate it once? Why would you use PHP to perform any data sorting or calculation if you don’t have to?
Database should take care of the data, not the underlying application.
that’s a recipe for disaster
yup, and rank in this example is an application concern
in my opinion
If it’s not a problem Rudy, can you please explain why it would be a recipe for disaster? I’m not trying to be rude, I’m just wondering what’s the reasoning behind it.
okay, no problem
how is the recalculation triggered?
what happens if it doesn’t work porpoerly?
is it possible for a “dirty” row to be retrieved before/while the recalculation is complete?
Just to ensure we’re on the same page, I’ll assume classic customer - invoices model.
1 customer can have multiple invoices. To simplify the whole process, let’s assume that balance is a sum of unpaid invoices minus the sum of paid invoices (let’s not go into debate about the table design, I’m just using this as an example).
So, to obtain John Doe’s balance, we’d have to use aggregate functions to calculate the balance.
So, why not use a trigger that will update customers table with the sum of unpaid - paid?
That way, when we want to find out someone’s balance, we don’t have to calculate what the current sum is. Or, even better - we want to know the total that we are being owed - we can run a sum on customers table rather than sum on invoices table - it’s less rows, hence it will be quicker.
Now, to answer the questions:
Recalculation is triggered by a trigger, on before insert, assuming that invoice isn’t updated. If yes, the same procedure - trigger controlled.
I am not sure what you mean by not working properly. Yes, there are number of reasons why something can go wrong, but that’s why we have the transactions available. As for the logic itself, if I am mistaken here - please, do correct me as I’d also like to know if it’s wrong.
No, it isn’t.