this is a SQL question that has always stumped me. how to order by the size of a column. for instance suppose many Presidents have many Votes and you want to list them in a ranking table. 1. Bob 345 Votes 2. Jim 215 Votes 3 Karen 121 Votes etc. what''s the SQL for that? is it possible in SQL to count columns within a query of Presidents and then order by the count? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
SELECT name, votes FROM presidents ORDER BY votes DESC On 4/16/07, plewizard <somethingnoonehasthoughtof-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > this is a SQL question that has always stumped me. how to order by > the size of a column. for instance suppose many Presidents have many > Votes and you want to list them in a ranking table. > > 1. Bob 345 Votes > 2. Jim 215 Votes > 3 Karen 121 Votes > > etc. what''s the SQL for that? is it possible in SQL to count columns > within a query of Presidents and then order by the count? > > > > >-- http://www.web-buddha.co.uk --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Are you saying here that you have two tables for Presidents and Votes. And you are wanting to sort by counting the related vote objects? If so you could do something like: SELECT presidents.name, count(votes.*) from presidents INNER JOIN votes ON president.id=votes.president_id ORDER BY count(votes.*) DESC; But, that''s terribly inefficient and will likely cause major performance problems. Your best bet is to use the facility in Rails to cache the vote counts inside your presidents table so that you can use a much more efficient SQL statement like: SELECT name, votes FROM presidents ORDER BY votes DESC; Look up information on the :counter_cache option of :belongs_to in the ActiveRecord documentation. That should point you in the right direction. On Apr 16, 10:41 am, "plewizard" <somethingnoonehasthough...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> this is a SQL question that has always stumped me. how to order by > the size of a column. for instance suppose many Presidents have many > Votes and you want to list them in a ranking table. > > 1. Bob 345 Votes > 2. Jim 215 Votes > 3 Karen 121 Votes > > etc. what''s the SQL for that? is it possible in SQL to count columns > within a query of Presidents and then order by the count?--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---