Ranking DB records

[Decaye]

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!

[allida77]

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

[BigDaddy]

Doh....beat me to it...I even had my sql book in front of me ready to type it up.

[Decaye]

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?

[BigDaddy]

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


No wait...that would only query the top 5 rows....

[A1ien51]

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

[A1ien51]

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

[BigDaddy]

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

Try that.

[allida77]

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.

[Decaye]

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?

[fractalvibes]

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

[Decaye]

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.

[glenngv]

I think Username is a reserved keyword in Access. Try changing it to another name.

[whammy]

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)!

[glenngv]

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.