# Combo Mysql/Rails/DBD question - How do I do rankings?

**URL:** <https://www.ruby-forum.com/t/combo-mysql-rails-dbd-question-how-do-i-do-rankings/144194>\
**Category:** Rails\
**Created:** [July 26, 2008, 4:04pm UTC](https://www.ruby-forum.com/t/combo-mysql-rails-dbd-question-how-do-i-do-rankings/144194 "2008-07-26T16:04:49Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![armstheykill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@armstheykill](https://www.ruby-forum.com/u/armstheykill)\
**Post date:** [July 26, 2008, 4:04pm UTC](https://www.ruby-forum.com/t/combo-mysql-rails-dbd-question-how-do-i-do-rankings/144194/1 "2008-07-26T16:04:49Z")

</div>

So I’m trying to be jack of all trades on a project, and instead i am  
mastering none.

Let’s say I have a table of users. Each user has a popularity score  
which is a non-negative float.

On each user’s homepage I want to display their rank. “Jimbo is  
ranked #3” Jimbo has the third highest number in the “popularity”  
column (out of all rows) at the moment that I pulled the row out of  
the DB.

Umm…how do I do this? I can think of a really expensive way  
involving a giant data load, a sort, and a count, but it’s not the  
rails way.

Any ideas? Thanks!

---

<div class="post-metadata">

**Author:** ![armstheykill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@armstheykill](https://www.ruby-forum.com/u/armstheykill)\
**Post date:** [July 26, 2008, 4:10pm UTC](https://www.ruby-forum.com/t/combo-mysql-rails-dbd-question-how-do-i-do-rankings/144194/2 "2008-07-26T16:10:40Z")

</div>

removed\_email\_address@domain.invalid wrote:

> On each user’s homepage I want to display their rank. “Jimbo is  
> ranked #3” Jimbo has the third highest number in the “popularity”  
> column (out of all rows) at the moment that I pulled the row out of  
> the DB.

rank = User.count(:conditions =\> [‘popularity \>= ?’, jimbo.popularity])

---

<div class="post-metadata">

**Author:** ![armstheykill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@armstheykill](https://www.ruby-forum.com/u/armstheykill)\
**Post date:** [July 26, 2008, 9:26pm UTC](https://www.ruby-forum.com/t/combo-mysql-rails-dbd-question-how-do-i-do-rankings/144194/3 "2008-07-26T21:26:19Z")

</div>

I think his point is “popularity” is NOT 3. It’s some float that is 3rd  
highest of all entries in the column.

I think you have to dip into the db specific features of the database to  
do  
this.

Usually this involves using a variable in the query in MySQL.

Personally I Think you should maintain a column that reflects the  
popularity  
rank, especially if you plan to paginate such results. I would do  
something  
like this: when a person’s popularity changes, using their current  
popularity and their new popularity, you should be able to find the  
specific  
people who need to be moved down or up 1. I would execute an UPDATE  
query  
that moves these people up/down 1. And then update the popluarity rank  
column of the person in question. That is really inexpensive to do.

Then you don’t need a giant data load. I wouldn’t try to do it with  
vendor  
specific SQL unless you truely needed to avoid adding a column.

That’s my $0.02

---

<div class="post-metadata">

**Author:** ![armstheykill](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/a587f6/32.png) [@armstheykill](https://www.ruby-forum.com/u/armstheykill)\
**Post date:** [July 28, 2008, 1:55am UTC](https://www.ruby-forum.com/t/combo-mysql-rails-dbd-question-how-do-i-do-rankings/144194/4 "2008-07-28T01:55:10Z")

</div>

Miles, you understood the problem correctly.

My point of view was that any time a user’s popularity changes I will  
have to run through the “ranking” column for every user. I can’t be  
sure that 50 will swap places with 49; it’s possible that 50 drops to  
55, so then I’ve got to re-rank everyone between 50 and 55, I guess.

Still worth doing in a column though, I guess.

Thanks for the idea.  
—Andrew