Krasic Krasic - 1 month ago 5
MySQL Question

MySQL Count + GroupBy & OrderBy and Last Record

I need to return Last Record on MySQL Count Query , however MySQL return always first record

table : author
+--------+----------------------+-----------+----------------+
| aut_id | aut_name | country | home_city |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton | UK | Cambridge |
| AUT002 | William Maugham | Canada | Toronto |
| AUT003 | William Anthony | UK | Leeds |
| AUT004 | S.B.Swaminathan | India | Bangalore |
| AUT005 | Thomas Morgan | Germany | Arnsberg |
| AUT006 | Thomas Merton | USA | New York |
| AUT007 | Piers Gibson | UK | London |
| AUT008 | Nikolai Dewey | USA | Atlanta |
| AUT009 | Marquis de Ellis | Brazil | Rio De Janerio |
| AUT010 | Joseph Milton | USA | Houston |
| AUT011 | John Betjeman Hunter | Australia | Sydney |
| AUT012 | Evan Hayek | Canada | Vancouver |
| AUT013 | E. Howard | Australia | Adelaide |
| AUT014 | C. J. Wilde | UK | London |
| AUT015 | Butler Andre | USA | Florida |
+--------+----------------------+-----------+----------------+
SELECT country,COUNT(*) as total , aut_name
FROM author
GROUP BY country
ORDER BY total desc;
+-----------+----------+


Problem is i get always first record on each country
such as John Betjeman Hunter on E. Howard & so..

NEEDED RESULT TO GET

+-----------+----------+
| country | COUNT(*) | aut_name
+-----------+----------+
| UK | 4 | C. J. Wilde ------> Last Entry in UK
| USA | 4 | Butler Andre ------> Last Entry in USA
| Australia | 2 | E. Howard
| Canada | 2 | Evan Hayek
| Brazil | 1 | Marquis de Ellis
| Germany | 1 | Thomas Morgan
| India | 1 | S.B.Swaminathan
+-----------+----------+

Answer
SELECT t2.country,
       t2.author_count,
       t1.aut_name
FROM author t1
INNER JOIN
(
    SELECT country, COUNT(*) AS author_count, MAX(aut_id) AS aut_id
    FROM author
    GROUP BY country
) t2
    ON t1.country = t2.country AND
       t1.aut_id  = t2.aut_id
ORDER BY t2.author_count DESC

Demo here:

SQLFiddle

Comments