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.
views:
76answers:
4
+5
A:
You can do that using LIMIT to set an offset.
SELECT field FROM table ORDER BY field DESC LIMIT 1,1;
Daniel Egeberg
2010-06-25 08:02:40
Thank you. That makes sense. I'll try that in a second !!
Nicolo' Verrini
2010-06-25 08:05:25
A:
SELECT
field,
COUNT(*) as cnt
FROM table
GROUP BY field
ORDER BY cnt DESC
LIMIT 1, 1
simendsjo
2010-06-25 08:05:21
+1
A:
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
Thariama
2010-06-25 08:06:56
+2
A:
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.
Vitalii Fedorenko
2010-06-25 08:09:35
That is great !I was not aware of that @syntax.Just another small question but if the field is INT. The MAX() function will catch up the HIGHER number or the MOST FOUND ? Thank you Vitalii
Nicolo' Verrini
2010-06-25 09:00:10
MAX() function finds the higher number. To find the most common value use: SELECT field, COUNT(*) as mx FROM table GROUP BY field ORDER BY mx DESC LIMIT 1;
Vitalii Fedorenko
2010-06-25 09:29:07