CODEWITHSUNDEEP
phpsqlmysqli
Thomas George
Thomas George

Reputation: 76

PHP SQL Find Top 5 Most Repeated Values

I have a database that I am accessing through PHP and mysqli. I need to get the top 5 most repeated values.

For example, the database would have in it 1, 1, 2, 2, 2, 4, 6, 5, 5, 5, 5,

The database should print out in a row

: 5, 2, 1, 4, 6

Upvotes: 0

Views: 331

Answers (3)

Arbels
Arbels

Reputation: 211

If you need the result as a string (in a row):

SELECT STUFF((SELECT ',' + convert(varchar,my_number) FROM my_table group by 
my_number ORDER BY count(my_number) desc for XML PATH('')), 1, 1, '')

Upvotes: 0

Jason Joslin
Jason Joslin

Reputation: 1144

Are you asking what the SQL query would be to get most occurances to least?

this here will group by the number and then order it from most occurances to the least

SELECT number , count(number) as 'occurrences' 
FROM my_table 
GROUP BY number 
ORDER BY count(number) DESC

Upvotes: 2

Sahil Gulati
Sahil Gulati

Reputation: 15141

Try this code snippet here

<?php
ini_set('display_errors', 1);
$array=array(1, 1, 2, 2, 2, 4, 6, 5, 5, 5, 5);
$data=array_count_values($array);//counting the no. of values in an array
arsort($data);//sorting array in descending order
print_r(array_keys($data));//getting the key of array which is the desired output

Upvotes: 0

Related Questions