no that won't work, because even though the "where" excludes *my* vote for a particular candidate, it will include everybody else's vote for the same candidate.
the objective is: if *i* voted for john, then john should not be in the final result set even though a million other people voted for john. picture the following list: john (11 votes) paul (8 votes) richard (6 votes) george (4 votes) now, if my vote is among the six votes for richard, then the final list should look like this, even though five other people voted for him: john (11 votes) paul (8 votes) george (4 votes) On 27/02/2008, Phil <[EMAIL PROTECTED]> wrote: > 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] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
