Ranking

[Guest]

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.

[Dan_Bracuk]

Does your db have a rank function?

[Guest]

Honestly I don't know. It's a MySQL 5 database.

[Dan_Bracuk]

It probably does. What happens when you google "mysql rank()"?

[Guest]

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.

[paross1]

Why can't you just count rows added, group by user, and order by count?

Phil

[Guest]

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?

[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

[Dan_Bracuk]

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.

[Newsgroup_User]

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>

[Guest]

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.

[Dan_Bracuk]

<cfif currentrow is TheRowYouWant>
<b>

and something later on for a closing tag

[Guest]

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.

[Newsgroup_User]

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

[Guest]

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.

[Newsgroup_User]

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.