CODEWITHSUNDEEP
phpmysqlsqlselect
Papa Charlie
Papa Charlie

Reputation: 635

select and get the most repeated occurrence on mysql

I have a table and i need to recover most repeated occurrence
in the example the occurrence repeats is kills:8
I not want to get the most value, i need get the most repeated value 

id | member | kills 
1  | -      | 9 
2  | -      | 8 
3  | -      | 4 
4  | -      | 8 
5  | -      | 7



thank you

Upvotes: 0

Views: 70

Answers (4)

Hrithu
Hrithu

Reputation: 281

Juz refer the link

SELECT kills,
       count(kills)
FROM TEMP
GROUP BY kills
ORDER BY count(kills) DESC LIMIT 1;

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71422

Group by kills, then order by count of the values in a descending manner, then limit to only one row.

SELECT
    kills,
    COUNT(id) AS kill_count
FROM table
GROUP BY kills
ORDER BY kill_count DESC
LIMIT 1

Upvotes: 2

user3555228
user3555228

Reputation: 31

My only idea is to run a while loop that counts each value in the kills column, tallies it and then runs if statements to get the highest tally. Hope this helps.

Upvotes: 0

PravinS
PravinS

Reputation: 2594

Try this query

SELECT kills, COUNT(id) FROM TABLE_NAME GROUP BY kills HAVING COUNT(id) > 1

Upvotes: 1

Related Questions