Hello monks,

I need enlightenment on a simple issue: What's an efficient way of retrieving a Rank # from a MySQL table of several hundred/thousand entries?

For example, I have a MySQL Table containing the data of every student including the number of classes they attended, their total scores, grades, and etc. The data from the table could easily be manipulated... like if I choose to display in a HTML Table the top100 students in the class, I'll just have the returns of the query arranged in an "ASC"ending manner, then determine their ranks.

My piece of concern is that if I retrieve the records for a single student... how will I determine its rank without retrieving the entire content of the table and sorting them or is this possible?

From my limited idea, I say this could be possible only if I retrieve all entries in the table and sort them, then from the array of the sorted entries, retrieve the rank and other data of the student. But what if the table becomes so large... speed might be a problem?

Please share your ideas... thanks!

Jay S.

In reply to What's an Efficient Way to Retrieve a Rank from a MySQL Table? by soon_j

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.