why this query result return wrong id ,name

Question

select id,name,type,MAX(vote) as maxvote from channelsvote group by type

Answer 1

mysql has no way to know which id and name are the "correct" ones if you group by type. it simply takes the first one. in standard sql this is not even possible, you'd have to specify some sort of aggregate function like you did for vote: MAX(id), MAX(name

Answer 2

Actually in standard SQL you can only select those fields that are either explicitly mentioned in the GROUP BY clause or an aggregation function is applied to them.

You can also read in the MySQL documentation about GROUP BY and HAVING with Hidden Columns:

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

Answer 3

It's not returning the wrong id and name, it's just your expectations of the result that is wrong.

You have specified grouping on the type field, and that you want the maximum value from vote from each group. You haven't specified which id and name you want from each group, so the database will just hand you any of the values.

As comparison, in MS SQL Server you can't run a query like that, as the result isn't guaranteed to make sense. You have to specify an aggregate for all fields in the result that you don't group on.

Answer 4

+----------------+
|ID | Name | Type|
+----------------+
| 1 | Andy |  A  |
+----------------+
| 2 | John |  A  |
+----------------+
| 3 | Clare|  B  |
+----------------+
| 4 | Danny|  B  |
+----------------+

I think grouping them up by type will just give you the 1st record of each group, i.e.,

1 | Andy | A
3 | Clare| B

If you're trying to display the number of votes each user has recieved, with the person having the highest number appearing first - I'm assuming you'd have a 'persons' sort of table (showing the person_id, name, etc) and a 'voted_person' kind of table (vote_id, person_id).

You could try:

SELECT 
  p.person_id, p.name, p.type, a.votes
FROM
  persons p, (SELECT person_id, count(*) as "votes" FROM voted_person GROUP BY
  person_id ORDER BY count(*) DESC) as a
WHERE
  p.id = a.id

The idea is to count the number of votes each person_id has by grouping them up by person_id and counting the number of records in each group.

Once that's done, a join could be done to the 'Person' table to retrieve more details associated with the person_id that appears in both table (i.e., persons with no votes will not show).

Sorry if the query doesn't work (or isn't what you are looking for), I'm still really new at SQL. But I hope it helps! :>