CODEWITHSUNDEEP
mysqlsql
Adriano Varoli Piazza
Adriano Varoli Piazza

Reputation: 7429

sql: select most voted items from each user

I have a table "vote_benefits" with the following schema: id (int 11) id_benefit (int 11) rating (int 11) cellphone (varchar 10) inputdate (date)

People can vote (through a stars rating system) for each item, giving it from 1 to 5 stars. I have to get the number of votes for each rating for each benefit.

The content is something like

id id_b rating cellphone  inputdate
1   2   5   123456789   2011-01-04 18:56:38
2   2   4   123456789   2011-01-04 19:03:27
3   2   4   123456789   2011-01-05 10:24:29
4   2   4   123456789   2011-01-05 10:24:33
5   2   5   123456789   2011-01-05 10:24:37
6   2   5   123456789   2011-01-05 10:24:42
7   2   4   123456789   2011-01-05 10:24:43
8   2   5   123456789   2011-01-05 10:24:44
9   2   4   123456789   2011-01-05 10:24:45
10  2   5   123456789   2011-01-05 10:24:48
11  2   5   123456789   2011-01-05 10:25:42
12  3   4   123456789   2011-01-05 10:49:20
13  3   5   123456789   2011-01-05 10:49:21
14  3   4   123456789   2011-01-05 10:49:22
15  3   5   123456789   2011-01-05 10:52:44
16  3   5   123456789   2011-01-05 10:52:45
17  3   4   123456789   2011-01-05 10:53:04
18  3   4   123456789   2011-01-05 10:53:05

I want to get from these the most voted benefits grouped by id_benefit. I got as far as

SELECT id_benefit, count( * ) AS votes, rating
FROM `vote_benefits 
GROUP BY id_benefit, rating

which gives me

id_b votes rating
2   5      4
2   6      5
3   4      4
3   3      5

But I'd like to group these by id_b, getting just the row with the most votes for each id_b:

id_b votes rating
2      6     5
3      4     4

I've read links like SQL - select distinct records in one field with highest records from another field , but I don't understand how to adapt it for my needs.

Upvotes: 1

Views: 1938

Answers (2)

kvista
kvista

Reputation: 5059

I believe the following should work:

SELECT id_b, MAX(votes), rating 
FROM (SELECT id_benefit, count( * ) AS votes, rating
        FROM `vote_benefits 
    GROUP BY id_benefit, rating) 
GROUP BY id_b

Upvotes: 1

ajreal
ajreal

Reputation: 47321

select 
id_b, max(rating_count), rating 
from 
(
  select id_b, rating, count(*) as rating_count 
  from vote_benefits 
  group by id_b, rating 
) as tbl
group by id_b;

Upvotes: 1

Related Questions