SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict AndyH's Avatar
    Join Date
    Aug 2003
    Location
    Australia
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Most votes (sub-queries maybe?)

    Well I need help with this one...

    I want to select the members ID with the most votes.

    Table: "leader_votes"
    `ID` mediumint(8)
    `MEM_ID ` mediumint(8)
    `vote_for` mediumint(8)
    `galaxy ` tinyint(3)
    `planet` enum('1', '2' ... '20')
    `MEM_ID` is the members ID who is voting
    `vote_for` is who the above member is voting for

    I want to select the members IDs (vote_for) with the most votes in a specific galaxy and planet - galaxy and planet are supplied by me.

    If you need anymore information just ask.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is a basic request, no join, nor subquery is necessary. What have you tried so far to get this query to work?

  3. #3
    SitePoint Addict AndyH's Avatar
    Join Date
    Aug 2003
    Location
    Australia
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't see how this is a basic request.

    I am not familiar with sub-queries so I am not sure how to go about doing this.

  4. #4
    SitePoint Zealot basbd's Avatar
    Join Date
    Oct 2005
    Location
    Oregon, USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try something like this:

    Code:
    SELECT vote_for
    FROM leader_votes
    GROUP BY vote_for
    ORDER BY count(vote_for) DESC
    LIMIT 1

  5. #5
    SitePoint Zealot basbd's Avatar
    Join Date
    Oct 2005
    Location
    Oregon, USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh, and if you want to limit by galaxy and planet:

    Code:
    SELECT vote_for
    FROM leader_votes
    WHERE planet = 'x' AND galaxy = 'y'
    GROUP BY vote_for
    ORDER BY count(vote_for) DESC
    LIMIT 1

  6. #6
    SitePoint Addict AndyH's Avatar
    Join Date
    Aug 2003
    Location
    Australia
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
     #1111 - Invalid use of group function
    Thanks for your help so far. Didn't think you could use COUNT() in ORDER BY...

    Quote Originally Posted by basbd
    Oh, and if you want to limit by galaxy and planet:

    Code:
        SELECT vote_for
        FROM leader_votes
        WHERE planet = 'x' AND galaxy = 'y'
        GROUP BY vote_for
        ORDER BY count(vote_for) DESC
        LIMIT 1

  7. #7
    SitePoint Enthusiast SecondV's Avatar
    Join Date
    Jan 2006
    Location
    Kentucky
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Possibly..

    Code:
    SELECT vote_for, COUNT(*)
    FROM leader_votes
    WHERE planet = 'x' AND galaxy = 'y'
    GROUP BY vote_for
    LIMIT 1;

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you guys are all pretty close

    SELECT vote_for, COUNT(*) as votes
    FROM leader_votes
    WHERE planet = 'x' AND galaxy = 'y'
    GROUP BY vote_for
    ORDER BY votes desc LIMIT 1;

    the important part is not necessarily assigning an alias name (yes, you could just as easily put the COUNT into the ORDER BY)

    the important part is including the count in the SELECT so that it becomes a result set column, which will allow you to sort on it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AndyH
    I don't see how this is a basic request.

    I am not familiar with sub-queries so I am not sure how to go about doing this.
    Too many people post homework questions in this and other forums. You had done nothing to show what approach you had tried so far in your query.

    As you see, there are no joins, no sub queries in the answer provided, hence it is a basic request.

    In future, if you are asking a question, it is always best to suggest how you have attempted to solve the question, even if in pseudo code, then folks will know you are trying to work out a solution, rather than have one handed to you which was done in this case.

    Some people don't have a problem with it, others of us do.

  10. #10
    SitePoint Addict AndyH's Avatar
    Join Date
    Aug 2003
    Location
    Australia
    Posts
    279
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help everyone and specifically r937.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •