SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    How does one get the rank of a row from a list

    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,

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT ( SELECT COUNT(*) + 1
               FROM users
              WHERE account_balance
                  > t.account_balance ) AS rank 
      FROM users AS t
     WHERE user = 'John Doe'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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,


    Quote Originally Posted by r937 View Post
    Code:
    SELECT ( SELECT COUNT(*) + 1
               FROM users
              WHERE account_balance
                  > t.account_balance ) AS rank 
      FROM users AS t
     WHERE user = 'John Doe'

    Anoox search engine volunteer

    www.anoox.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    why do you want to generate a list?

    i thought you wanted john doe's rank

    what is the list for?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Quote Originally Posted by r937 View Post
    why do you want to generate a list?

    i thought you wanted john doe's rank

    what is the list for?

    Anoox search engine volunteer

    www.anoox.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.




    Quote Originally Posted by r937 View Post
    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


    Anoox search engine volunteer

    www.anoox.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    ... but I was wondering if there is a command in MySQL that would do that for us? I guess not.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  11. #11
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by furicane View Post
    Yes, when the data is changed you recalculate the value.
    that's a recipe for disaster

    Quote Originally Posted by furicane View Post
    Database should take care of the data, not the underlying application.
    yup, and rank in this example is an application concern

    in my opinion

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by furicane View Post
    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

    1. how is the recalculation triggered?

    2. what happens if it doesn't work porpoerly?

    3. is it possible for a "dirty" row to be retrieved before/while the recalculation is complete?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    187
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    1) Recalculation is triggered by a trigger, on before insert, assuming that invoice isn't updated. If yes, the same procedure - trigger controlled.

    2) 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.

    3) No, it isn't.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •