Most prolific Writer. Mysql Query.

[DarkMonkey]

I know now that my database design wasn't too good when I made my articles script but... I can't (be bothered?) to change it now, so anyway. I have a table it has fields a bit like this.

Writer | Title | Content | Date Written

there are more but they are irrelivent. Basically, what I want to do, is grab the name (writer) that appears the most often in the table, the most prolific writer, if you will.

I have been told by several sources that this can be done with the GROUP comand, but I'm still not sure how, if somebody could tell me how it's done, and possibly, if they have time, give me a brief explanation of how GROUP (BY) works, I'd be most grateful indeed

[7stud]

Hi,

I am not real familiar with GROUP BY, but I know basically what it is. If you had this query:

SELECT max(salary)
FROM employee;

you would get the maximum salary from the salary column of your employee table. If you alter the statement like this:

SELECT max(salary), department
FROM employee
GROUP BY department;

then you would get the maximum salary for each distinct department value in your table, i.e. several values rather than one value, AND the department name would be listed for every max(salary).

Here is an interactive tutorial on GROUP BY. Look at the Aggregate Functions first. If you do the brief exercises you will get a basic understanding.

http://www.sqlcourse2.com/groupby.html

For your purposes, I think you could do something like this:

SELECT COUNT(Title), Writer
FROM mytable
GROUP BY Writer;

That would return rows consisting of the total number of articles by an author followed by the author's name. I think it will list the authors alphabetically A-Z.

[freakysid]

SELECT COUNT(*) AS works, Writer
FROM tableName
GROUP BY Writer
ORDER BY works DESC
LIMIT 1

That should return a result set with one row containing the number of works ("works") and the name ("Writer") of the most prolific writer.

[DarkMonkey]

Thanks people, your the best