CODEWITHSUNDEEP
phpmysqlsql
user3542456
user3542456

Reputation:

How to find top 5 duplicate entries in a MySQL Database?

I'm trying to find the highest number of duplicate rows in a SQL table. I'm able to get the count, but the number will dynamically change from time to time. So would I need to add the count to the table and then find the max of the count column? Or would I just need to find the max of the counted column without adding it to the table? I hope those questions make sense. Here's what I have so far.

$countQuery = "SELECT breakers, COUNT(breakers) AS num FROM $tblEmerg GROUP BY breakers";
$count = mysqli_query($db, $countQuery) or die(mysqli_error($db));
    while($row = mysqli_fetch_assoc($count)){
        echo "<p>" .$row['breakers'] ." - " .$row['num'] ."</p>";
    }

This gives me the output of:

APU [1] - 3    
APU [5] - 1    
APU [7] - 1    
AVIONICS[1] - 2    
FC[5] - 1    
FC[6] - 1    
FC[7] - 1    
FC[9] - 1

Which is what I want, but as I mentioned, the 'num' will change dynamically and I want to just return the top 5 results. How would I go about doing this?

Upvotes: 1

Views: 484

Answers (3)

Vickrant
Vickrant

Reputation: 1313

I think this will give you the required result.

SELECT breakers, COUNT(breakers) AS num FROM $tblEmerg GROUP BY breakers ORDER BY COUNT(breakers) DESC LIMIT 5

Upvotes: 0

Vikas Kumar
Vikas Kumar

Reputation: 1733

Try this:

SELECT breakers, COUNT(breakers) AS num 
  FROM $tblEmerg 
  GROUP BY breakers having num > 1 order by count(breakers) desc limit 5

Upvotes: 1

Oerd
Oerd

Reputation: 2313

You just need to add ORDER BY COUNT(breakers) DESC LIMIT 5 to your query.

Upvotes: 0

Related Questions