# Top 10 in mysql?

**URL:** <https://www.ruby-forum.com/t/top-10-in-mysql/111415>\
**Category:** Rails\
**Created:** [August 7, 2007, 4:05am UTC](https://www.ruby-forum.com/t/top-10-in-mysql/111415 "2007-08-07T04:05:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sketchy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@Sketchy](https://www.ruby-forum.com/u/Sketchy)\
**Post date:** [August 7, 2007, 4:05am UTC](https://www.ruby-forum.com/t/top-10-in-mysql/111415/1 "2007-08-07T04:05:49Z")

</div>

Hi, I’m new to rails and I’m trying to find the top ten posted url in  
my mysql db… so let’s say my db name is links and each item has two  
fields (userid and url)… I want to find the top 10 posted url by  
count… how can I do that? I know I have to use the find() method but  
I’m having trouble with count()…

thanks in advance

---

<div class="post-metadata">

**Author:** ![Sketchy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@Sketchy](https://www.ruby-forum.com/u/Sketchy)\
**Post date:** [August 7, 2007, 4:32pm UTC](https://www.ruby-forum.com/t/top-10-in-mysql/111415/2 "2007-08-07T16:32:12Z")

</div>

> Hi, I’m new to rails and I’m trying to find the top ten posted url in  
> my mysql db… so let’s say my db name is links and each item has two  
> fields (userid and url)… I want to find the top 10 posted url by  
> count… how can I do that? I know I have to use the find() method but  
> I’m having trouble with count()…

Assuming you are using a sql database, the non-rails way to do this is:  
select url, count(url)  
from my\_table  
group by url  
limit 10

Using Active Record you need to look atthe :group and :limit  
attributes.

Good luck!

---

<div class="post-metadata">

**Author:** ![Sketchy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@Sketchy](https://www.ruby-forum.com/u/Sketchy)\
**Post date:** [August 7, 2007, 5:41pm UTC](https://www.ruby-forum.com/t/top-10-in-mysql/111415/3 "2007-08-07T17:41:21Z")

</div>

thanks for your response but I want to do it by Active Record… I  
have tried using :group but my object is always nil…

@links = Links.find(:all, :group =\> “url”, :limit =\> 10)

using mysql it would be something like this :  
select count(url) as total, url from links group by url order by total  
DESC limit 10

How does this translate in Active recod?

---

<div class="post-metadata">

**Author:** ![Sketchy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@Sketchy](https://www.ruby-forum.com/u/Sketchy)\
**Post date:** [August 8, 2007, 2:00am UTC](https://www.ruby-forum.com/t/top-10-in-mysql/111415/4 "2007-08-08T02:00:27Z")

</div>

Sketchy, a couple of things, one calling find() on a model object  
expects to be able to return to you a set of model objects. A  
count (or sum, …) aren’t generally in your model so its  
hard to coerce the select into a model form.

Second, you have to tell the database sometime to  
actually do some counting. The following would work if you  
have in your Links table some number column called  
“counter”

@links = Links.find(:all, :select =\> “url, count(\*) as counter”,  
:group =\> “url”,  
:order =\> “counter desc”,  
:limit =\> 10)

Note that you need the order clause because it will  
sort all the grouped links in decending order, then  
you need limit to only get the top 10.

Also not that you must return column names that fit into the  
model Links - otherwise model objects can’t be returned.

---

<div class="post-metadata">

**Author:** ![Sketchy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/258eb7/32.png) [@Sketchy](https://www.ruby-forum.com/u/Sketchy)\
**Post date:** [August 8, 2007, 6:35am UTC](https://www.ruby-forum.com/t/top-10-in-mysql/111415/5 "2007-08-08T06:35:46Z")

</div>

Thank you very much, I got it working!