Sort by size of column

Rails
1

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?

2

SELECT name, votes FROM presidents ORDER BY votes DESC

On 4/16/07, plewizard [email protected] 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

3

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”