Find the record with the lest amount of associated records
I just asked this question Find the oldest record in a join between two tables and got a great answer to my question. The problem is that is is not quite what I was looking for (my fault)
Consider the following MySQL tables
Table: Questions ID Table: Results ID Created - When this record was added. Q_ID - A FK to the Question table
Table: Questions ID ---- 1 8 15 55 Table: Results ID | Created | Q_ID -------------------- 1 | 12:02 | 1 2 | 12:03 | 15 3 | 12:04 | 8
Using the following query, it will return all the records that do not have results associated with them, if all records have results then it will return the question with the oldest result.
SELECT * FROM questions LEFT JOIN results ON results.q_id = questions.id ORDER BY ISNULL(results.id) DESC, results.created ASC LIMIT 1
What I am actually looking for is for any question that has not been answered, then to sort the question my the count of how many times they have been answered. The lest answered questions should be at the top.
This would give you each question, and the number of results associated it (even if no results exist). They will be ordered with the lowest count at the top:
SELECT Questions.ID, COUNT(Results.ID) Result_Count FROM Questions LEFT JOIN Results ON Questions.ID = Results.Q_ID GROUP BY Questions.ID ORDER BY COUNT(Results.ID)
Is this what you had in mind?