top 10 in mysql?

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

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!

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?

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.

Thank you very much, I got it working!