Ok then, so select candidate,count(*) as total from vote where (voter <> '$me' and vote =1) group by candidate order by total desc;
On Wed, Feb 27, 2008 at 9:37 AM, Olav Mørkrid <[EMAIL PROTECTED]> wrote: > hi phil, i forgot to mention one thing. > > the table also has a column called "vote" which is either 0 (no vote > given) or 1 (vote given). this column is required for other purposes. > > my favorites: > select candidate from vote where voter = '$me' and vote = 1; > > most popular: > select candidate from vote where vote = 1 > group by candidate order by count(*) desc; > > when generating the desired list (most popular minus my favorites) it > is important that a candidate is excluded from the result set if *i* > voted for him -- even if a million other people voted for him. > > is this clearer? > > On 27/02/2008, Phil <[EMAIL PROTECTED]> wrote: > > I'm confused as to why you need the subselect at all? > > > > As it's all the same table why can't you just use > > > > select candidate,count(*) as total from vote where voter <> '$me' group > by > > candidate order by total > > desc; > > > > On Wed, Feb 27, 2008 at 9:04 AM, Olav Mørkrid <[EMAIL PROTECTED]> > > wrote: > > > > > hello > > > > > > i have a table "vote" which has the columns "voter" and "candidate". i > > > would like to make a list of the most popular candidates *except* > > > those who are on my favorite list. using a sub-select, it's easy: > > > > > > my favorites: > > > select candidate from vote where voter = '$me'; > > > > > > most popular: > > > select candidate from vote group by candidate order by count(*) desc; > > > > > > sub-select: > > > select candidate from vote where candidate not in (select candidate > > > from vote where voter = '$me') group by candidate order by count(*) > > > desc; > > > > > > however, sub-selects are very slow, so i need to find a speedy way. > > > i'm familiar with joins, but don't know how to use it for this case > > > where grouping is involved. > > > > > > please get in touch if you know how to solve it. > > > > > > thanks! > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
