Choose company with most records mysql

Question

I have a mysql database with records from different companies. I need to select records from companies which have the most, second most and third most records and plot their number of records per year. How do I select them?

Many thanks.

EDIT:

The table would look something like this:

Company    Year
A          1999
A          1999
B          1999
C          1999
A          2000
C          2000
A          2003

So if I select the company with the most records, A has the most records, and the output is;

Year    Total
1999     2
2000     1
2003     1

And for the company with second most records, the output is ( in this case, company C)

Year    Total
1999     1
2000     1

Third most will be company B.

Answer 1

I'd say something like

SELECT company,COUNT(company) AS rec,year
FROM your_table GROUP BY company, year ORDER BY rec DESC LIMIT 3;

Answer 2

Something like this could do the trick:

select recordcount from companies order by recordcount DESC limit 3;

Answer 3

most_frequent:

SELECT year, COUNT(year) AS total FROM your_table 
WHERE company = 
    (SELECT company, COUNT(company) AS c
     FROM your_table
     GROUP BY c
     ORDER BY c DESC
     LIMIT 0,1)

second most:

SELECT year, COUNT(year) AS total FROM your_table 
WHERE company = 
    (SELECT company, COUNT(company) AS c
     FROM your_table
     GROUP BY c
     ORDER BY c DESC
     LIMIT 1,1)

third most:

SELECT year, COUNT(year) AS total FROM your_table 
WHERE company = 
    (SELECT company, COUNT(company) AS c
     FROM your_table
     GROUP BY c
     ORDER BY c DESC
     LIMIT 2,1)

In order to get two dimensional output in one query:

SELECT company, year, COUNT(year) AS total FROM your_table 
INNER JOIN
    (SELECT company, COUNT(company) AS c
     FROM your_table
     GROUP BY c
     ORDER BY c DESC) AS t1
ON your_table.company = t1.company
ORDER BY t1.c DESC

I'm sorry, I haven't tested it. Just leave a comment if you have any trouble.