How to select values by the count of a filed?

Question

How to select values in MySQL table by the count of a field in the same table ?

Example:

Table users has id, name, password, country

I want to select all ids of only top 3 countries with highest users count to look like this:

country, id

When I try this
WHERE country IN (SELECT country FROM USERS GROUP BY country ORDER BY COUNT(id) DESC LIMIT 3)

I get This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Server version: 5.6.33-79.0 Percona Server

Rápli András · Answer 1 · 2016-12-14T22:55:18.773

0

How to select values in MySQL table by the count of a field in the same table?

Use the GROUP BY field HAVING COUNT(*)=123 aggregation expression after the WHERE clause if there is.

edited Dec 14 '16 at 22:55

answered Dec 14 '16 at 22:49

Rápli András

3,869
1
35
55

how will HAVING COUNT(field)=123 will help here? I need to order the table by count first. – Nagy Wesley Dec 14 '16 at 22:55
You asked two different questions. I answered the first of them. – Rápli András Dec 14 '16 at 22:55

Jibin Balachandran · Answer 2 · 2016-12-14T23:29:55.957

0

Try this:

SELECT country, id 
FROM
      (
         SELECT country,count(Id) as [NumberofUsers]
         FROM USERS 
         GROUP BY country 
                           ) X
INNER JOIN USERS U ON X.Country=U.Country
ORDER BY X.[NumberofUsers] DESC LIMIT 3

edited Dec 14 '16 at 23:29

answered Dec 14 '16 at 22:56

Jibin Balachandran

3,381
1
24
38

That looks right, but you are assuming that country table has an id field, thus `COUNT(*)` is preferred. – Rápli András Dec 14 '16 at 23:02
the error is `This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'` – Nagy Wesley Dec 14 '16 at 23:11
@NagyWesley Updated my answer, do check and let me know if you get any issue. – Jibin Balachandran Dec 14 '16 at 23:21
The `ORDER BY NumberofUsers DESC LIMIT 3` should be inside the select statement to work, anyway I've accepted @Barmar answer... thanks for the help – Nagy Wesley Dec 14 '16 at 23:26

score 0 · Accepted Answer · answered Dec 14 '16 at 23:15

Join with a subquery that gets the top 3 countries.

SELECT u.country, u.id
FROM users AS u
JOIN (SELECT country
      FROM users
      GROUP BY country
      ORDER BY COUNT(*) DESC
      LIMIT 3) AS u1
ON u.country = u1.country

Practically any use of WHERE column IN (SELECT ...) can be changed to a JOIN like this, and in many cases the performance is better.

How to select values by the count of a filed?

3 Answers3