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?
on 2007-04-16 16:42
on 2007-04-16 16:45
SELECT name, votes FROM presidents ORDER BY votes DESC On 4/16/07, plewizard <firstname.lastname@example.org> wrote: > 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
on 2007-04-16 17:38
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"