Ranking

Forum|Forum|17 years ago
July 7, 2008
12 replies
877 views

This shouldn't be hard but I am stuck. I am running a query that adds the number of rows a person has added to three different tables. What I want to do with that query is determine where that total is in relation to all other users that have added rows. So lets say I've added 1 row to each table and I total 3 rows. That ranks me #3 out of 10 total users.

How would I end up with that ranking? I've gotten as far as the totalling part.

This topic has been closed for replies.

N

Newsgroup_User

Inspiring

idesdema wrote:
> That's what I thought too. However, it is not working. The problem is that
> the variable that stores the username on cflogin is the same variable called
> username in the query results. So it bolds everything.
>

Sounds like you are comparing the wrong data, but it's hard to say
without seeing some code. You must have a field that has different
usernames in each row, and this is the field that needs to be compared
to the current user.

A

Anonymous

That's what I thought too. However, it is not working. The problem is that the variable that stores the username on cflogin is the same variable called username in the query results. So it bolds everything.

N

Newsgroup_User

Inspiring

idesdema wrote:
> But I don't know what the row I want is. See, I'm one user of many with a
> points table that is changing all the time. I want the code to say "you are
> user1 and are row 5 of 23" or something like that. So then when row 5 displays
> I can bold that font.
>

This is a basic if statement in your output loop.

...
<cfif thisUser EQ query.userColumn>
Display *BOLD* row
<cfelse>
Display normal row
</cfif>
...

A

Anonymous

But I don't know what the row I want is. See, I'm one user of many with a points table that is changing all the time. I want the code to say "you are user1 and are row 5 of 23" or something like that. So then when row 5 displays I can bold that font.

D

Dan_Bracuk

Inspiring

<cfif currentrow is TheRowYouWant>
<b>

and something later on for a closing tag

A

Anonymous

I have the current row figured out (I was numbering the results) but I am stuck with highlighting my particular row in the result set.

I want the row where my username is to be bold and I can't figure out how to do it.

N

Newsgroup_User

Inspiring

idesdema wrote:
> How do you display the current row of a recordset. I know query.recordcount
> gives me all the results, but what if I wanted to determine what row contains
> my user id? So let's say there are 10 rows in a query, and row 6 has my
> user_id. How could I determine that it is row 6?
>

1) Investigate ColdFusion's Query-of-a-Query functionality.

AND|OR

2) Investigate how to reference a ColdFusion record set using full
'array' notation.

HINT:
<cfoutput>
#aQuery.aColumn[aRow]#
OR
#aQuery['aColumn'][aRow]#
<cfoutput>

A

Anonymous

How do you display the current row of a recordset. I know query.recordcount gives me all the results, but what if I wanted to determine what row contains my user id? So let's say there are 10 rows in a query, and row 6 has my user_id. How could I determine that it is row 6?

P

paross1

Participating Frequently

Maybe I'm missing something, and I don't know what tables you are trying to count rows in, but did you try something like this?

SELECT userid, count(*) AS rank
FROM your_table
GROUP BY userid
ORDER BY count(*)

Phil

D

Dan_Bracuk

Inspiring

quote:

Originally posted by: paross1
Maybe I'm missing something, and I don't know what tables you are trying to count rows in, but did you try something like this?

SELECT userid, count(*) AS rank
FROM your_table
GROUP BY userid
ORDER BY count(*)

Phil

That won't answer his question. Apparently mySql does not have a rank function so he'll have to loop through the results of that query to find his userid and see what the current row is.

A

Anonymous

I took an alternate path to the solution. I setup a new table that keeps track of each user's score based on the action they take that results in points. This will end up being the easiest way to do this for me.