0

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

Alen Giliana
  • 2,144
  • 3
  • 17
  • 30
Curtis Crentsil
  • 459
  • 6
  • 20

2 Answers2

0
select `userid`, MAX(`votecount`)
FROM `audio`
GROUP BY `userId` ORDER BY `votecount`
drum
  • 5,416
  • 7
  • 57
  • 91
  • this just gets the highest votecount but not the correct row, if i edit this query to : `SELECT date,votecount,promoted,status,image,name,id,userId, MAX(votecount) FROM audio GROUP BY userId ORDER BY votecount;` it gets the first row with the userId and just replaces the votecount with the highest votecount – Curtis Crentsil Dec 23 '22 at 17:41
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
Alen Giliana
  • 2,144
  • 3
  • 17
  • 30