Submit Your Article Forum Rules
Results 1 to 8 of 8

Thread: mysql_query

  1. #1


    hi wpw

    in the database there are multiple records for the same person.
    for example

    person 1 has 5 entries
    person 2 has 3 entries
    and so forth.

    all im trying to do is get a simple count of who has the most entries from highes to lowest.

    i wrote this query it works well for me but im using mysql version 5.1.33 locally.

    SELECT mname, COUNT(mname) FROM votes GROUP BY mname ORDER BY COUNT(mname) DESC

    the server this will run on uses a older version of mysql. im not sure which one though.

    any ideas on how i can rewrite this?
    It's better to do business with me than against me!

  2. #2

    Re: mysql_query

    That looks perfectly fine for whatever version of MySQL you need...certainly on MySQL 5.0, and 4.x...

    Is there a reason you think it won't work? GROUP BY and COUNT are pretty standard stuff.

    As far as the query itself, you might want to group by the person ID (if there is one), rather than the name (in case 2 people have the same name like "John Smith"), otherwise no problem.

  3. #3

    Re: mysql_query

    I'd agree with dmartin about compatibility (and ID). Have you actually tried (and failed) to run this on the live server yet or are you just assuming it will have problems?

  4. #4

    Re: mysql_query

    Thanks Uncle Dog and dmartin.
    i should have mentioned that detail.

    I tried to run it.
    The error i receive is:
    Invalid use of group function

    if i run it without ORDER BY COUNT(mname) DESC
    it will output all the information but not in order.


    the version of mysql that's live is 3.23.58
    It's better to do business with me than against me!

  5. #5

    Re: mysql_query

    Hmmm....still seems valid, but try this:

    SELECT mname, COUNT(mname) as numvotes FROM votes
    GROUP BY mname
    ORDER BY numvotes DESC

    Maybe using an alias is needed in 3.23?

  6. #6

    Re: mysql_query

    thanks dmartin

    That worked out fine.

    I dont know why my original way didn't work.

    Thanks again
    It's better to do business with me than against me!

  7. #7

    Re: mysql_query

    Glad that worked - old MySQL versions are a little quirky in terms of the SQL they support - my guess is that expressions without aliases don't work in 3.23 with a GROUP BY...who knows.

    Maybe your installation can eventually upgrade to something better supported - 3.23 (and 4.0 and 4.1) have been "end of lifed".

  8. #8

    Re: mysql_query

    I believe this issue is that in older versions of MySQL you couldn't run a group function outside of the SELECT - in your case, COUNT. I think this was an optimization issue, the second count would require a new database call, whereas using an alias allows you to sort the results by the COUNT you already did in the SELECT statement.
    The best way to learn anything, is to question everything.
    Hidden Content

Posting Permissions

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