Preliminary comment
Please learn to use the explicit JOIN notation, not the old (pre-1992) implicit join notation.
Old style:
SELECT transactionTable.rating as MostCommonRating
FROM personTable, transactionTable
WHERE personTable.transactionid = transactionTable.transactionid
AND personTable.personid = 1
GROUP BY transactionTable.rating
ORDER BY COUNT(transactionTable.rating) desc
LIMIT 1
Preferred style:
SELECT transactionTable.rating AS MostCommonRating
FROM personTable
JOIN transactionTable
ON personTable.transactionid = transactionTable.transactionid
WHERE personTable.personid = 1
GROUP BY transactionTable.rating
ORDER BY COUNT(transactionTable.rating) desc
LIMIT 1
You need an ON condition for each JOIN.
Also, the personID values in the data are strings, not numbers, so you'd need to write
WHERE personTable.personid = "Ben"
for example, to get the query to work on the tables shown.
Main answer
You're seeking to find an aggregate of an aggregate: in this case, the maximum of a count. So, any general solution is going to involve both MAX and COUNT. You can't apply MAX directly to COUNT, but you can apply MAX to a column from a sub-query where the column happens to be a COUNT.
Build the query up using Test-Driven Query Design — TDQD.
Select person and transaction rating
SELECT p.PersonID, t.Rating, t.TransactionID
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
Select person, rating, and number of occurrences of rating
SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
This result will become a sub-query.
Find the maximum number of times the person gets any rating
SELECT s.PersonID, MAX(s.RatingCount)
FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
) AS s
GROUP BY s.PersonID
Now we know which is the maximum count for each person.
Required result
To get the result, we need to select the rows from the sub-query which have the maximum count. Note that if someone has 2 Good and 2 Bad ratings (and 2 is the maximum number of ratings of the same type for that person), then two records will be shown for that person.
SELECT s.PersonID, s.Rating
FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
) AS s
JOIN (SELECT s.PersonID, MAX(s.RatingCount) AS MaxRatingCount
FROM (SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
) AS s
GROUP BY s.PersonID
) AS m
ON s.PersonID = m.PersonID AND s.RatingCount = m.MaxRatingCount
If you want the actual rating count too, that's easily selected.
That's a fairly complex piece of SQL. I would hate to try writing that from scratch. Indeed, I probably wouldn't bother; I'd develop it step-by-step, more or less as shown. But because we've debugged the sub-queries before we use them in bigger expressions, we can be confident of the answer.
WITH clause
Note that Standard SQL provides a WITH clause that prefixes a SELECT statement, naming a sub-query. (It can also be used for recursive queries, but we aren't needing that here.)
WITH RatingList AS
(SELECT p.PersonID, t.Rating, COUNT(*) AS RatingCount
FROM PersonTable AS p
JOIN TransactionTable AS t
ON p.TransactionID = t.TransactionID
GROUP BY p.PersonID, t.Rating
)
SELECT s.PersonID, s.Rating
FROM RatingList AS s
JOIN (SELECT s.PersonID, MAX(s.RatingCount) AS MaxRatingCount
FROM RatingList AS s
GROUP BY s.PersonID
) AS m
ON s.PersonID = m.PersonID AND s.RatingCount = m.MaxRatingCount
This is simpler to write. Unfortunately, MySQL does not yet support the WITH clause.
The SQL above has now been tested against IBM Informix Dynamic Server 11.70.FC2 running on Mac OS X 10.7.4. That test exposed the problem diagnosed in the preliminary comment. The SQL for the main answer worked correctly without needing to be changed.
