SQL query records for MAX () Max () in a category field in a table

The problem is:

the database has a table code with a click Field Click_num and a category field kind and other information fields.

now to find out the most hits in each category of the record, if it is 10 categories, then the result should be 10 records,

if the maximum number of hits is two the same as one.

after n searches, n-times the solution SQL statement on the Internet, finally found an elegant and results-based SQL, this is a blog author found in the official MySQL document.

Can't help collecting, for later use.

2. Select id,kind,click_num from code as a
4. where click_num= (Select max(b.click_num)
6. From code as b
8. where a.kind = B.kind
10.                   );

Special attention:

This statement is tested on SQLite and is fast and normal. But on my machine on the MySql5.0 on the execution of the crash, other versions of MySQL do not know what the situation.

The above statement does not satisfy my second requirement: if the maximum number of hits in a category has two identical records, only one.

Here are my additional workarounds:

filter, ensure that each category has only one, (Kind,id to all columns, the result is the corresponding record)

2. Select *
4. from select from as
6.                             < Span style= "color: #0000FF; font-weight:bold;background-color:inherit;" >where select max
8.                                                  from  code   b  
10. where a.kind = B.kind
12.                                              )  
14. ) as a
16. Group by kind

Follow-up:

One of the biggest problems with MySQL in the past is that in the WHERE clause of a query, if you include a query for the table in the previous select, the CPU usage will soar and the MySQL service stops. Can be said to be a self-connected query problem, do not know whether this is a bug,5.0,5.1 version of the problem, look back to see the above SQL statement just conforms to this problem, the solution is to query the table into a temporary table or view in short, and the previous select table is the same table, Then the problem is solved, so that the above SQL can be slightly modified to run on MySQL.

Modify the process:

1. Create a view max_click that stores the maximum number of clicks per category

2. create view as select max as
4. From code
6. Group by kind;

2. Filter to ensure only one record per category

2. Select *
4. From (Select id,kind,click_num
6. From code
8. where click_num = ( Select b.click_num
10. From max_click as b
12. where code.kind = B.kind
14.                            )  
16. ) as a
18. Group by kind;

Normal operation, speed 0.78 seconds. (altogether 23,000 data in the table) okay! Done!

the first SQL running on MSSQL do not know the line, I think in SQLite can be normal in MSSQL should be no problem, it seems that MySQL needs to improve a lot of Ah, also is open source SQLite can do, and sqlite so lightweight on hundreds of KB, MySQL should be able to do more.

SQL query records for MAX () Max () in a category field in a table