How to find most repeating element from a table?

I have a table tbl_user and I want to find which user_name(s) have the most repeating values in the table. I only want to retrieve tho开发者_StackOverflowse user_id(s).

I also need a separate query to find the count the of duplicate user_name(s) in the table .

First query to find the biggest amount of repeated usernames:

  SELECT user_name, COUNT(user_id) AS amount
    FROM tbl_user
GROUP BY user_name
ORDER BY amount DESC

And then grab id`s:

# faster way using username from last query

  SELECT user_id
    FROM tbl_user
   WHERE user_name = '$user_name'


# longer way

  SELECT user_id
    FROM tbl_user
   WHERE user_name = (
       SELECT user_name
         FROM tbl_user
     GROUP BY user_name
     ORDER BY COUNT(amount) DESC
        LIMIT 1
   )

To find the count of duplicates:

SELECT count(id) AS num_duplicates FROM tbl_users GROUP BY user_name ORDER BY num_duplicates DESC

To find the user_id's of the most duplicated user_name:

SELECT user_id FROM tbl_user 
    WHERE username IN ( SELECT user_id 
                           FROM tbl_users 
                        GROUP BY user_name 
                        ORDER BY count(id) DESC LIMIT 0, 1)

SELECT COUNT(*) FROM tbl_user
GROUP BY tbl_user.user_name

Will give you the number of duplicate user_names. You can use a scripting language to select out the highest one if you like, or just look at the list if it's not something you need automated.

If you mean you want to count the occurences of a particular username, something like this may work for you.

select user_name,user_id,count(*) as mycount from tbl_user group by user_name order by mycount desc limit 5

This should get the top 5 entries

You could select the user_names which are duplicated with this SQL:

SELECT COUNT(1) AS duplicates, user_name
FROM tbl_user
GROUP BY user_name
HAVING duplicates > 1

Having done this, you can then use it as a subquery to find out the count of duplicate user_names

SELECT COUNT(1) AS count_of_user_names, SUM(duplicates) AS duplicate_records
FROM (
  SELECT COUNT(1) AS duplicates, user_name
  FROM tbl_user
  GROUP BY user_name
  HAVING duplicates > 1
) AS subquery

Or use it to find out the user ids:

SELECT user_id
FROM tbl_user
WHERE user_name IN (
  SELECT user_name
  FROM tbl_user
  GROUP BY user_name
  HAVING COUNT(1) > 1
)