How to get individual rows with maximum value

Question

My apologies for asking what should be very easy to solve, but I just can’t imagine how this is deceiving me. I could not even come up with a really suitable heading for my question, I apologize for this as well.

I have a survey in which each user can post several answers to a question, and others can vote for these answers. I need to get the result when the highest voice of each user is returned.

Test case: let's say a question like "What is your favorite quote from a song?"

CREATE TABLE `answers` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `authorId` INT, `answer` TEXT NOT NULL , `votes` INT NOT NULL ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO `answers` VALUES (1, 30, "The West is the Best", 120), (2, 30, "Come on, baby, light my fire", 100), (3, 31, "Everything gonna be allright", 350), (4, 31, "Sayin' oooh, I love you", 350), (5, 31, "Singing sweet songs of melodies pure and true", 290), (6, 32, "I'm your pole and all you're wearing is your shoes", 540), (7, 32, "And I'm crazier when I'm next to her", 180), (8, 32, "You hear the music in the air", 230), (9, 30, "You know they are a liar", 190) 

The result I expect to get:

 id | authorId | answer | votes 6 | 32 | I'm your pole and all you're wearing is your shoes | 540 3 | 31 | Everything gonna be allright | 350 9 | 30 | You know they are a liar | 190 

Basically, I need to choose the best answer for each author, and then sort the result by the votes that received the best answers. It may happen that two answers of the same author have the same number of votes; then only the first one (lower identifier) ​​should be selected (as shown with answers No. 3 and No. 4). Two results of the answer of one author may never appear as a result - each author can win only once.

I searched and searched, tried and tried again, and at the moment I feel like a brainwashed. Perhaps this is not feasible in a single SQL query; it should be like this, maybe it's worth noting that the application is written in PHP. I know that I can just take all the answers ORDER BY votes desc, id asc , and then authorId over the result, remember all the authorId and throw authorId any line with authorId that I already saw, but I need to get the given number from the records, and this can become inconvenient (... it may be necessary to execute the query again with an offset if I lay out too many lines, etc.) .. but in the end it might be the best solution if the solution with one query was overly complex or not at all. ..

Any ideas ?: Oh)

Answer 1

 SELECT id, authorId, answer, votes FROM ( SELECT id, authorId, answer, votes FROM answers ORDER BY votes DESC) AS h GROUP BY authorId 

This little neat trick is based on GROUP BY to extract the first row of each case. Usually this is the default ORDER BY id ASC , but through this helper request, the first line in each authorId with the highest votes .

Note. As Iain Elder already mentioned, this solution does not work with ONLY_FULL_GROUP_BY active and only works in MySQL. This solution is not supported to some extent due to the lack of documentation supporting this behavior. It works well for me and always works well for me.

This method still works with the latest MySQL on sqlfiddle .

Answer 2

You can use a subselect:

 select min(a1.id), a1.authorid, a2.mxvotes from answers a1 inner join ( select authorid, max(votes) mxvotes from answers group by authorid ) a2 on a1.authorid = a2.authorid and a1.votes = a2.mxvotes group by a1.authorid, a2.mxvotes order by mxvotes desc 

see SQL Fiddle with Demo

Answer 3

Great question, Dan.

MySQL does not have analytic functions to make this easy to solve. A similar question was asked by Oracle and was resolved using the OVER clause with the MAX function. It also works on SQL Server.

You need to use subqueries for this in MySQL. This works for me:

 SELECT id, authorId, answer, votes FROM answers AS firsts WHERE id = ( SELECT MIN(id) FROM answers AS favorites WHERE votes = ( SELECT MAX(votes) FROM answers AS author_max WHERE author_max.authorId = favorites.authorID ) AND favorites.authorId = firsts.authorId ) ORDER BY votes DESC; 

See the sqlfiddle example for the executable.

Answer 4

 select * from (select * from answers order by votes desc) as temp group by authorId