CODEWITHSUNDEEP
mysqlsqlcount
Gooey
Gooey

Reputation: 4778

SQL query - select max where a count greater than value

I've got two tables with the following structure:

Question table

id int, 
question text, 
answer text, 
level int

Progress table

qid int, 
attempts int, 
completed boolean (qid means question id)

Now my questions is how to construct a query that selects the max level where the count of correct questions is greater than let's say 30.

I created this query, but it doesn't work and I don't know why.

SELECT MAX(Questions.level) 
FROM Questions, Progress 
WHERE Questions.id = Progress.qid AND Progress.completed = 1 
GROUP BY  Questions.id, Questions.level 
Having COUNT(*) >= 30

I would like to have it in one query as I suspect this is possible and probably the most 'optimized' way to query for it. Thanks for the help!

Upvotes: 0

Views: 3157

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Do it step by step rather than joining the two tables. In an inner select find the questions (i.e. the question ids) that were answered 30 times correctly. In an outer select find the corresponding levels and get the maximum value:

select max(level) 
from questions
where id in
(
  select qid
  from progress 
  where completed = 1
  group by qid
  having count(*) >= 30
);

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

This sort of construct will work. You can figure out the details.

select max(something) maxvalue
from SomeTables
join (select id, count(*) records
from ATable
group by id) temp on ATable.id = temp.id
where records >= 30

Upvotes: 1

Related Questions