Question 21612 in Mysql Question posted in
The official MySQL documentation can be found here.

how to select all by highest from a table where the value of a column does not reapet – Mysql

i want to select row by the highest votecount from a table, but if the userid is repeted i want the just the row with the highest votecount. eg:

userId votecount
2 2
2 12
1 20

my result is supposed to be:

1,20
2,12

this is my current code:

SELECT * from `audio` GROUP BY `userId` ORDER BY `votecount` DESC LIMIT 50

the result of my code is :

1,20
2,2

it’s grouping by userId and then ordering it by votecount, which is not the desired output

Tags:

2

Answers


  1. 0 
    select `userid`, MAX(`votecount`)
FROM `audio`
GROUP BY `userId` ORDER BY `votecount`
    Login or Signup to reply.
  2. 0

    If that’s the case and you don’t have a unique row id, you need to add the other columns in the group by clause

    SELECT date, promoted, status, image, name, id, userId, MAX(votecount)  
FROM audio 
GROUP BY date, promoted, status, image, name, id, userId
ORDER BY votecount DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search