Simple MySQL question - MAX(field)?

Hi just a simple question

I know how to get the most recurring field from a table

SELECT MAX(开发者_运维百科field) FROM table

But how do I get the Second most recurring field from a table?

Thank you.

You can do that using LIMIT to set an offset.

 SELECT field FROM table ORDER BY field DESC LIMIT 1,1;

If performance is crucial for you, this can help to avoid sorting:

SET @a := (SELECT MAX(field) FROM table);
SELECT MAX(field) FROM table WHERE field != @a;

As alternative you can store @a value in code.

Definition (from about.com): Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results. It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display). Also Known As: Range Results Examples:

 SELECT * FROM `your_table` LIMIT 0, 10 

will display the first 10 results from the database.

 SELECT * FROM `your_table` LIMIT 5, 5 

will show records 6, 7, 8, 9, and 10

 SELECT * FROM `your_table` ORDER BY 'FIELD' DESC LIMIT 1, 1 

will show the second most recurring record

SELECT
    field,
    COUNT(*) as cnt
FROM table
GROUP BY field
ORDER BY cnt DESC
LIMIT 1, 1