Mysql: row order by frequency value

Question

Let's say I have this table:

+----+------+---------+
| Id | Item | Country |
+----+------+---------+
|  1 | b123 | Austria |
|  2 | a123 | Italy   |
|  3 | b990 | Germany |
|  4 | h231 | Austria |
|  5 | y233 | France  |
|  6 | u223 | Austria |
|  7 | p022 | Spain   |
|  8 | d133 | Italy   |
|  9 | w112 | Germany |
| 10 | j991 | Austria |
+----+------+---------+

I want to do SELECTin this table and order the results with which I Countryrepeat the most. Thus, the expected result should be:

+----+------+---------+
| Id | Item | Country |
+----+------+---------+
|  1 | b123 | Austria |
|  4 | h231 | Austria |
|  6 | u223 | Austria |
| 10 | j991 | Austria |
|  2 | a123 | Italy   |
|  8 | d133 | Italy   |
|  3 | b990 | Germany |
|  9 | w112 | Germany |
|  5 | y233 | France  |
|  7 | p022 | Spain   |
+----+------+---------+

How can i do this?

I tried this:

SELECT * FROM items WHERE Item != '' GROUP BY Item HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

But this will return something like this:

+----+------+---------+
| Id | Item | Country |
+----+------+---------+
|  1 | b123 | Austria |
|  8 | d133 | Italy   |
|  3 | b990 | Germany |
|  5 | y233 | France  |
|  7 | p022 | Spain   |
+----+------+---------+

Answer 1

A - Original table
B - Getting the counts at Country Level.

By combining A and B, we can sort the data in descending order of quantity, and also display all the elements from the table.

SELECT A.*
  FROM items A
INNER JOIN 
(    SELECT Country,COUNT(*) AS cnt       
      FROM items 
     WHERE Item != '' 
     GROUP BY Item 
) B
   ON A.Country = B.Country
ORDER BY B.cnt DESC,A.Country,A.Id; 

Answer 2

You can include a subquery in order by. Thus, one way:

select i.*
from items i
where i.item <> ''
order by (select count(*) from items i2 where i2.item = i.item) desc;

This approach has advantages and disadvantages compared to the execution group byand accession to the value:

• : items(item).
• : where , .
• : where , .