Web Analytics Made Easy -
StatCounter Ranking DB records - CodingForum

Announcement

Collapse
No announcement yet.

Ranking DB records

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Ranking DB records

    Say I have a table. This is a scaled down version of the actual table. Call it tblusers. 3 columns with the header name of USERNAME, Zone 1, Zone 2

    Example table:
    USERNAME |zone1| Zone 2
    john |3| 1
    pete |2| 3
    rick |5| 7

    The numbers are just points. What I want to do is create a string of code that will add the points for each user, then display order highest to lowest the username and his total points. Let's just say we want the top 5.
    So say i use a Select statement like

    Select USERNAME, (zone1 + zone2) FROM tblUsers

    to get the username and his corresponding total, but ranked according to the total, high to low and display it in an asp page?

    According to those example points, Rick would have 12, pete would have 5, and John would 4.
    So we'd want a display like

    RICK 12
    PETE 5
    JOHN 4

    I hope i was clear on how to ask this question. Thanks!

  • #2
    Select USERNAME, (zone1 + zone2) as totalPoints FROM tblUsers OrderBy totalPoints DESC
    does this sig match?

    Comment


    • #3
      Doh....beat me to it...I even had my sql book in front of me ready to type it up.
      This is in spanish when you're not looking.

      Comment


      • #4
        Yes, but that would select all, i need to only display the top 5. Can you show me a sample of what you would do to accomplish that? Oh and how would you throw that info into a table
        With a loop or something?

        Comment


        • #5
          Select USERNAME, (zone1 + zone2) as totalPoints FROM tblUsers Where rownum < 6 OrderBy totalPoints DESC


          No wait...that would only query the top 5 rows....
          This is in spanish when you're not looking.

          Comment


          • #6
            SELECT Amt_ID1 + Amt_ID2 AS total
            FROM yourTable
            ORDER BY total DESC

            basic idea.....

            guess I should not talk on the phone then submit the answer
            Last edited by A1ien51; Feb 26, 2004, 04:59 PM.
            Tech Author [Ajax In Action, JavaScript: Visual Blueprint]

            Comment


            • #7
              if you are looping throught to write out the info then only loop for the top 5....

              depending on the database you might be able to add

              SELECT TOP 5 col1, col2, col3........
              Last edited by A1ien51; Feb 26, 2004, 05:01 PM.
              Tech Author [Ajax In Action, JavaScript: Visual Blueprint]

              Comment


              • #8
                SELECT TP.USERNAME, TP.TOTALPOINTS FROM (
                Select USERNAME, (zone1 + zone2) as totalPoints FROM tblUsers OrderBy totalPoints DESC) TP
                WHERE TP.ROWNUM < 6

                Try that.
                This is in spanish when you're not looking.

                Comment


                • #9
                  What is your db? Depending on it you can either to a

                  Select TOP 5 USERNAME, (zone1 + zone2) as totalPoints FROM tblUsers OrderBy totalPoints DESC

                  or

                  Select TOP 5 USERNAME, (zone1 + zone2) as totalPoints FROM tblUsers OrderBy totalPoints DESC FETCH FIRST 5 ROWS ONLY


                  either way would be the most effiecient.
                  does this sig match?

                  Comment


                  • #10
                    Ok, i see where I might be confusing you guys. It's an Access DB, but i don't need to get the first five rows. I need to display the top 5 users, ordered by totalpoints into a displayable table. I'd rather not repsonse.write the table html. Just stick the recordset in there and do a loop. Thing is, i can see that if i just put say
                    <%rs=('totalpoints')%> into one table cell. It would display it all there. Should i try and use and array so that if i display it the usernames can be in their own cell and the points are in their own cell. I.E give usernames a variable and give points a variable?

                    Comment


                    • #11
                      I believe the sequel Alida posted should work - it fetches a resultset ordered by total points, descending, then fetches the first 5 of those

                      so you have
                      row1 200 points
                      row2 188 points
                      row3 166 points
                      row4 158 points
                      row5 153 points
                      ------cut off here----
                      row6 148 points
                      row7 144 points

                      etc.

                      fv

                      Comment


                      • #12
                        When I use this code to use your suggestion...

                        <%
                        set rsTop=Server.CreateObject("ADODB.Recordset")
                        SQLstr = "Select TOP 5 Username, (Zone1 + Zone2) as Points FROM tblUsers ORDER BY Points DESC"
                        rsTop.Open SQLstr, adocon
                        %>

                        For some reason, the rsTop.open Sqlstr, adocon line is giving me

                        Microsoft JET Database Engine error '80040e10'

                        No value given for one or more required parameters.

                        I use the same open statement for everything, I'm not saying the SQL statement is causing problems, Just wondering why I'm getting that error at all.

                        Comment


                        • #13
                          I think Username is a reserved keyword in Access. Try changing it to another name.
                          Glenn
                          vBulletin Mods That Rock!

                          Comment


                          • #14
                            It is... you can also just do this instead of renaming the field:

                            SQLstr = "Select TOP 5 [Username], (Zone1 + Zone2) as Points FROM tblUsers ORDER BY Points DESC"

                            However, it's generally a good practice to avoid using reserved words in any language (unfortunately, it's generally cases like this when you find another reserved word you didn't know about that can be a problem... the only reason I know "Username" is reserved in Access is because of the very first server-side login script that I created)!
                            Last edited by whammy; Feb 27, 2004, 12:35 AM.
                            Former ASP Forum Moderator - I'm back!

                            If you can teach yourself how to learn, you can learn anything. ;)

                            Comment


                            • #15
                              Along with Username is the keyword Password. Sometime ago, my colleagues had the whole day debugging an application when I suddenly approached them and saw their problem and then tell them at once that username and password are reserved keywords. That got them mad and very frustrated but they had a sigh of relief.
                              Glenn
                              vBulletin Mods That Rock!

                              Comment

                              Working...
                              X