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]
